<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Appendix A MySQL 8.0 Frequently Asked Questions</title>
<link rel="stylesheet" href="mvl.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets + chunker.py v1.9.2" />
<link rel="start" href="index.html" title="{book-title}" />
<link rel="up" href="" title="" />
<link rel="prev" href="workbench.html" title="Chapter 30 MySQL Workbench" />
<link rel="next" href="error-handling.html" title="Appendix B Errors, Error Codes, and Common Problems" />
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Appendix A MySQL 8.0 Frequently Asked Questions</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="workbench.html">Prev</a> </td>
<th width="60%" align="center"></th>
<td width="20%" align="right"> <a accesskey="n" href="error-handling.html">Next</a></td>
</tr>
</table>
<hr>
</div>
<div class="appendix">
<div class="titlepage">
<div>
<div>
<h1 class="title"><a name="faqs"></a>Appendix A MySQL 8.0 Frequently Asked Questions</h1>

</div>

</div>

</div>
<div class="toc">
<p><b>Table of Contents</b></p><dl class="toc"><dt><span class="section"><a href="faqs.html#faqs-general">A.1 MySQL 8.0 FAQ: General</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-storage-engines">A.2 MySQL 8.0 FAQ: Storage Engines</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-sql-modes">A.3 MySQL 8.0 FAQ: Server SQL Mode</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-stored-procs">A.4 MySQL 8.0 FAQ: Stored Procedures and Functions</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-triggers">A.5 MySQL 8.0 FAQ: Triggers</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-views">A.6 MySQL 8.0 FAQ: Views</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-information-schema">A.7 MySQL 8.0 FAQ: INFORMATION_SCHEMA</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-migration">A.8 MySQL 8.0 FAQ: Migration</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-security">A.9 MySQL 8.0 FAQ: Security</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-mysql-cluster">A.10 MySQL 8.0 FAQ: MySQL Cluster</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-cjk">A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean
Character Sets</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-connectors-apis">A.12 MySQL 8.0 FAQ: Connectors &amp; APIs</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-replication">A.13 MySQL 8.0 FAQ: Replication</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-thread-pool">A.14 MySQL 8.0 FAQ: MySQL Enterprise Thread Pool</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-innodb-change-buffer">A.15 MySQL 8.0 FAQ: InnoDB Change Buffer</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-tablespace-encryption">A.16 MySQL 8.0 FAQ: InnoDB Tablespace Encryption</a></span></dt><dt><span class="section"><a href="faqs.html#faqs-virtualization">A.17 MySQL 8.0 FAQ: Virtualization Support</a></span></dt></dl>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-general"></a>A.1 MySQL 8.0 FAQ: General</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899453330672"></a><dl><dt>A.1.1. <a href="faqs.html#faq-mysql-version-ga">
        Which version of MySQL is production-ready (GA)?
      </a></dt><dt>A.1.2. <a href="faqs.html#faq-mysql-version-non-ga">
        What is the state of development (non-GA) versions?
      </a></dt><dt>A.1.3. <a href="faqs.html#faq-mysql-why-8.0">
        Why did MySQL version numbering skip versions 6 and 7 and go
        straight to 8.0?
      </a></dt><dt>A.1.4. <a href="faqs.html#faq-mysql-do-subqueries">
        Can MySQL 8.0 do subqueries?
      </a></dt><dt>A.1.5. <a href="faqs.html#faq-mysql-do-multiple-iud">
        Can MySQL 8.0 perform multiple-table inserts,
        updates, and deletes?
      </a></dt><dt>A.1.6. <a href="faqs.html#faq-mysql-have-sequences">
        Does MySQL 8.0 have Sequences?
      </a></dt><dt>A.1.7. <a href="faqs.html#faq-mysql-have-now-fractions">
        Does MySQL 8.0 have a
        NOW() function with fractions of
        seconds?
      </a></dt><dt>A.1.8. <a href="faqs.html#faq-mysql-support-multi-core">
        Does MySQL 8.0 work with multi-core processors?
      </a></dt><dt>A.1.9. <a href="faqs.html#faq-mysql-why-multiple-processes">
        Why do I see multiple processes for mysqld?
      </a></dt><dt>A.1.10. <a href="faqs.html#faq-mysql-have-acid-transactions">
        Can MySQL 8.0 perform ACID transactions?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-mysql-version-ga"></a><a name="idm139899453330016"></a><p><b>A.1.1.</b></p></td><td align="left" valign="top"><p>
        Which version of MySQL is production-ready (GA)?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        MySQL 5.7 and MySQL 5.6 are supported for production use.
      </p><p>
        MySQL 5.7 achieved General Availability (GA) status with MySQL
        5.7.9, which was released for production use on 21 October 2015.
      </p><p>
        MySQL 5.6 achieved General Availability (GA) status with MySQL
        5.6.10, which was released for production use on 5 February
        2013.
      </p><p>
        MySQL 5.5 achieved General Availability (GA) status with MySQL
        5.5.8, which was released for production use on 3 December 2010.
        The MySQL 5.5 series is no longer current, but still supported
        in production.
      </p><p>
        MySQL 5.1 achieved General Availability (GA) status with MySQL
        5.1.30, which was released for production use on 14 November
        2008. Active development for MySQL 5.1 has ended.
      </p><p>
        MySQL 5.0 achieved General Availability (GA) status with MySQL
        5.0.15, which was released for production use on 19 October
        2005. Active development for MySQL 5.0 has ended.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-version-non-ga"></a><a name="idm139899453324976"></a><p><b>A.1.2.</b></p></td><td align="left" valign="top"><p>
        What is the state of development (non-GA) versions?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        MySQL follows a milestone release model that introduces
        pre-production-quality features and stabilizes them to release
        quality (see
        <a class="ulink" href="http://dev.mysql.com/doc/mysql-development-cycle/en/index.html" target="_top">http://dev.mysql.com/doc/mysql-development-cycle/en/index.html</a>).
        This process then repeats, so releases cycle between
        pre-production and release quality status. Please check the
        change logs to identify the status of a given release.
      </p><p>
        MySQL 8.0, a successor to MySQL 5.7, is being actively developed
        using the milestone release methodology described above.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-why-8.0"></a><a name="idm139899453321536"></a><p><b>A.1.3.</b></p></td><td align="left" valign="top"><p>
        Why did MySQL version numbering skip versions 6 and 7 and go
        straight to 8.0?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Due to the many new and important features we were introducing
        in this MySQL version, we decided to start a fresh new series.
        As the series numbers 6 and 7 had actually been used before by
        MySQL, we went to 8.0.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-do-subqueries"></a><a name="idm139899453319264"></a><p><b>A.1.4.</b></p></td><td align="left" valign="top"><p>
        Can MySQL 8.0 do subqueries?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. See <a class="xref" href="sql-syntax.html#subqueries" title="13.2.11 Subquery Syntax">Section 13.2.11, “Subquery Syntax”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-do-multiple-iud"></a><a name="idm139899453316624"></a><p><b>A.1.5.</b></p></td><td align="left" valign="top"><p>
        Can MySQL 8.0 perform multiple-table inserts,
        updates, and deletes?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. For the syntax required to perform multiple-table updates,
        see <a class="xref" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax">Section 13.2.12, “UPDATE Syntax”</a>; for that required to perform
        multiple-table deletes, see <a class="xref" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax">Section 13.2.2, “DELETE Syntax”</a>.
      </p><p>
        A multiple-table insert can be accomplished using a trigger
        whose <code class="literal">FOR EACH ROW</code> clause contains multiple
        <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statements within a
        <code class="literal">BEGIN ... END</code> block. See
        <a class="xref" href="stored-programs-views.html#triggers" title="23.3 Using Triggers">Section 23.3, “Using Triggers”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-sequences"></a><a name="idm139899453309312"></a><p><b>A.1.6.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 have Sequences?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No. However, MySQL has an <code class="literal">AUTO_INCREMENT</code>
        system, which in MySQL 8.0 can also handle inserts
        in a multi-master replication setup. With the
        <a class="link" href="replication.html#sysvar_auto_increment_increment"><code class="literal">auto_increment_increment</code></a> and
        <a class="link" href="replication.html#sysvar_auto_increment_offset"><code class="literal">auto_increment_offset</code></a> system
        variables, you can set each server to generate auto-increment
        values that don't conflict with other servers. The
        <a class="link" href="replication.html#sysvar_auto_increment_increment"><code class="literal">auto_increment_increment</code></a> value
        should be greater than the number of servers, and each server
        should have a unique offset.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-now-fractions"></a><a name="idm139899453302384"></a><p><b>A.1.7.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 have a
        <a class="link" href="functions.html#function_now"><code class="literal">NOW()</code></a> function with fractions of
        seconds?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes, see <a class="xref" href="data-types.html#fractional-seconds" title="11.3.6 Fractional Seconds in Time Values">Section 11.3.6, “Fractional Seconds in Time Values”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-support-multi-core"></a><a name="idm139899453298512"></a><p><b>A.1.8.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 work with multi-core processors?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. MySQL is fully multi-threaded, and will make use of
        multiple CPUs, provided that the operating system supports them.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-why-multiple-processes"></a><a name="idm139899453296416"></a><p><b>A.1.9.</b></p></td><td align="left" valign="top"><p>
        Why do I see multiple processes for <code class="literal">mysqld</code>?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        When using LinuxThreads, you should see a minimum of three
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> processes running. These are in fact
        threads. There is one thread for the LinuxThreads manager, one
        thread to handle connections, and one thread to handle alarms
        and signals.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-acid-transactions"></a><a name="idm139899453292192"></a><p><b>A.1.10.</b></p></td><td align="left" valign="top"><p>
        Can MySQL 8.0 perform ACID transactions?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. All current MySQL versions support transactions. The
        <code class="literal">InnoDB</code> storage engine offers full ACID
        transactions with row-level locking, multi-versioning,
        nonlocking repeatable reads, and all four SQL standard isolation
        levels.
      </p><p>
        The <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top"><code class="literal">NDB</code></a> storage engine supports the
        <a class="link" href="innodb-storage-engine.html#isolevel_read-committed"><code class="literal">READ COMMITTED</code></a> transaction
        isolation level only.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-storage-engines"></a>A.2 MySQL 8.0 FAQ: Storage Engines</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899453285712"></a><dl><dt>A.2.1. <a href="faqs.html#faq-mysql-what-storage-engines">
        Where can I obtain complete documentation for MySQL storage
        engines?
      </a></dt><dt>A.2.2. <a href="faqs.html#faq-mysql-have-new-storage-engines">
        Are there any new storage engines in MySQL 8.0?
      </a></dt><dt>A.2.3. <a href="faqs.html#faq-mysql-removed-storage-engines">
        Have any storage engines been removed in MySQL 8.0?
      </a></dt><dt>A.2.4. <a href="faqs.html#faq-mysql-disabling-storage-engines">
        Can I prevent the use of a particular storage engine?
      </a></dt><dt>A.2.5. <a href="faqs.html#faq-mysql-innodb-backup-recovery-advantage">
        Is there an advantage to using the InnoDB
        storage engine exclusively, as opposed to a combination of
        InnoDB and non-InnoDB
        storage engines?
      </a></dt><dt>A.2.6. <a href="faqs.html#faq-mysql-what-archive-engine">
        What are the unique benefits of the ARCHIVE
        storage engine?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-mysql-what-storage-engines"></a><a name="idm139899453284896"></a><p><b>A.2.1.</b></p></td><td align="left" valign="top"><p>
        Where can I obtain complete documentation for MySQL storage
        engines?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        See <a class="xref" href="storage-engines.html" title="Chapter 16 Alternative Storage Engines">Chapter 16, <i>Alternative Storage Engines</i></a>. That chapter contains
        information about all MySQL storage engines except for the
        <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> storage engine and the
        <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top"><code class="literal">NDB</code></a> storage engine (used for MySQL
        Cluster). <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> is covered in
        <a class="xref" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine">Chapter 15, <i>The InnoDB Storage Engine</i></a>.
        <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top"><code class="literal">NDB</code></a> is covered in
        <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top">MySQL NDB Cluster 7.5 and NDB Cluster 7.6</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-new-storage-engines"></a><a name="idm139899453275920"></a><p><b>A.2.2.</b></p></td><td align="left" valign="top"><p>
        Are there any new storage engines in MySQL 8.0?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No. <code class="literal">InnoDB</code> is the default storage engine for
        new tables. See <a class="xref" href="innodb-storage-engine.html#innodb-introduction" title="15.1 Introduction to InnoDB">Section 15.1, “Introduction to InnoDB”</a> for
        details.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-removed-storage-engines"></a><a name="idm139899453272464"></a><p><b>A.2.3.</b></p></td><td align="left" valign="top"><p>
        Have any storage engines been removed in MySQL 8.0?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The <code class="literal">PARTITION</code> storage engine plugin which
        provided partitioning support is replaced by a native
        partitioning handler. As part of this change, the server can no
        longer be built using
        <code class="option">-DWITH_PARTITION_STORAGE_ENGINE</code>.
        <code class="literal">partition</code> is also no longer displayed in the
        output of <a class="link" href="sql-syntax.html#show-plugins" title="13.7.6.25 SHOW PLUGINS Syntax"><code class="literal">SHOW PLUGINS</code></a>, or shown
        in the <a class="link" href="information-schema.html#plugins-table" title="24.15 The INFORMATION_SCHEMA PLUGINS Table"><code class="literal">INFORMATION_SCHEMA.PLUGINS</code></a>
        table.
      </p><p>
        In order to support partitioning of a given table, the storage
        engine used for the table must now provide its own
        (<span class="quote">“<span class="quote">native</span>”</span>) partitioning handler.
        <a class="link" href="innodb-storage-engine.html" title="Chapter 15 The InnoDB Storage Engine"><code class="literal">InnoDB</code></a> is the only storage engine
        supported in MySQL 8.0 that includes a native partitioning
        handler. An attempt to create partitioned tables in MySQL 8.0
        using any other storage engine fails. (The
        <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top"><code class="literal">NDB</code></a> storage engine used by MySQL
        Cluster also provides its own partitioning handler, but is
        currently not supported by MySQL 8.0.)
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-disabling-storage-engines"></a><a name="idm139899453262192"></a><p><b>A.2.4.</b></p></td><td align="left" valign="top"><p>
        Can I prevent the use of a particular storage engine?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. The
        <a class="link" href="server-administration.html#sysvar_disabled_storage_engines"><code class="literal">disabled_storage_engines</code></a>
        configuration option defines which storage engines cannot be
        used to create tables or tablespaces. By default,
        <a class="link" href="server-administration.html#sysvar_disabled_storage_engines"><code class="literal">disabled_storage_engines</code></a> is
        empty (no engines disabled), but it can be set to a
        comma-separated list of one or more engines.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-innodb-backup-recovery-advantage"></a><a name="idm139899453257408"></a><p><b>A.2.5.</b></p></td><td align="left" valign="top"><p>
        Is there an advantage to using the <code class="literal">InnoDB</code>
        storage engine exclusively, as opposed to a combination of
        <code class="literal">InnoDB</code> and non-<code class="literal">InnoDB</code>
        storage engines?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. Using <code class="literal">InnoDB</code> tables exclusively can
        simplify backup and recovery operations. MySQL Enterprise Backup
        does a <a class="link" href="glossary.html#glos_hot_backup" title="hot backup">hot backup</a> of all
        tables that use the <code class="literal">InnoDB</code> storage engine.
        For tables using <code class="literal">MyISAM</code> or other
        non-<code class="literal">InnoDB</code> storage engines, it does a
        <span class="quote">“<span class="quote">warm</span>”</span> backup, where the database continues to run,
        but those tables cannot be modified while being backed up. See
        <a class="xref" href="mysql-enterprise.html#mysql-enterprise-backup" title="29.2 MySQL Enterprise Backup Overview">Section 29.2, “MySQL Enterprise Backup Overview”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-what-archive-engine"></a><a name="idm139899453248128"></a><p><b>A.2.6.</b></p></td><td align="left" valign="top"><p>
        What are the unique benefits of the <code class="literal">ARCHIVE</code>
        storage engine?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The <code class="literal">ARCHIVE</code> storage engine stores large
        amounts of data without indexes; it has a small footprint, and
        performs selects using table scans. See
        <a class="xref" href="storage-engines.html#archive-storage-engine" title="16.5 The ARCHIVE Storage Engine">Section 16.5, “The ARCHIVE Storage Engine”</a>, for details.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-sql-modes"></a>A.3 MySQL 8.0 FAQ: Server SQL Mode</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899453243184"></a><dl><dt>A.3.1. <a href="faqs.html#faq-mysql-what-sql-modes">
        What are server SQL modes?
      </a></dt><dt>A.3.2. <a href="faqs.html#faq-mysql-count-sql-modes">
        How many server SQL modes are there?
      </a></dt><dt>A.3.3. <a href="faqs.html#faq-mysql-how-see-sql-mode">
        How do you determine the server SQL mode?
      </a></dt><dt>A.3.4. <a href="faqs.html#faq-mysql-sql-mode-dependency">
        Is the mode dependent on the database or connection?
      </a></dt><dt>A.3.5. <a href="faqs.html#faq-mysql-extend-strict-mode">
        Can the rules for strict mode be extended?
      </a></dt><dt>A.3.6. <a href="faqs.html#faq-mysql-strict-impact">
        Does strict mode impact performance?
      </a></dt><dt>A.3.7. <a href="faqs.html#faq-mysql-what-default-mode">
        What is the default server SQL mode when MySQL 8.0
        is installed?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-mysql-what-sql-modes"></a><a name="idm139899453242432"></a><p><b>A.3.1.</b></p></td><td align="left" valign="top"><p>
        What are server SQL modes?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Server SQL modes define what SQL syntax MySQL should support and
        what kind of data validation checks it should perform. This
        makes it easier to use MySQL in different environments and to
        use MySQL together with other database servers. The MySQL Server
        apply these modes individually to different clients. For more
        information, see <a class="xref" href="server-administration.html#sql-mode" title="5.1.8 Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-count-sql-modes"></a><a name="idm139899453239472"></a><p><b>A.3.2.</b></p></td><td align="left" valign="top"><p>
        How many server SQL modes are there?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Each mode can be independently switched on and off. See
        <a class="xref" href="server-administration.html#sql-mode" title="5.1.8 Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>, for a complete list of available
        modes.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-see-sql-mode"></a><a name="idm139899453236640"></a><p><b>A.3.3.</b></p></td><td align="left" valign="top"><p>
        How do you determine the server SQL mode?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        You can set the default SQL mode (for <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a>
        startup) with the <a class="link" href="server-administration.html#option_mysqld_sql-mode"><code class="option">--sql-mode</code></a>
        option. Using the statement
        <a class="link" href="sql-syntax.html#set-variable" title="13.7.5.1 SET Syntax for Variable Assignment"><code class="literal">SET
        [GLOBAL|SESSION]
        sql_mode='<em class="replaceable"><code>modes</code></em>'</code></a>, you can
        change the settings from within a connection, either locally to
        the connection, or to take effect globally. You can retrieve the
        current mode by issuing a <code class="literal">SELECT @@sql_mode</code>
        statement.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-sql-mode-dependency"></a><a name="idm139899453229776"></a><p><b>A.3.4.</b></p></td><td align="left" valign="top"><p>
        Is the mode dependent on the database or connection?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        A mode is not linked to a particular database. Modes can be set
        locally to the session (connection), or globally for the server.
        you can change these settings using
        <a class="link" href="sql-syntax.html#set-variable" title="13.7.5.1 SET Syntax for Variable Assignment"><code class="literal">SET
        [GLOBAL|SESSION]
        sql_mode='<em class="replaceable"><code>modes</code></em>'</code></a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-extend-strict-mode"></a><a name="idm139899453225952"></a><p><b>A.3.5.</b></p></td><td align="left" valign="top"><p>
        Can the rules for strict mode be extended?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        When we refer to <span class="emphasis"><em>strict mode</em></span>, we mean a
        mode where at least one of the modes
        <a class="link" href="server-administration.html#sqlmode_traditional"><code class="literal">TRADITIONAL</code></a>,
        <a class="link" href="server-administration.html#sqlmode_strict_trans_tables"><code class="literal">STRICT_TRANS_TABLES</code></a>, or
        <a class="link" href="server-administration.html#sqlmode_strict_all_tables"><code class="literal">STRICT_ALL_TABLES</code></a> is enabled.
        Options can be combined, so you can add restrictions to a mode.
        See <a class="xref" href="server-administration.html#sql-mode" title="5.1.8 Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>, for more information.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-strict-impact"></a><a name="idm139899453218992"></a><p><b>A.3.6.</b></p></td><td align="left" valign="top"><p>
        Does strict mode impact performance?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The intensive validation of input data that some settings
        requires more time than if the validation is not done. While the
        performance impact is not that great, if you do not require such
        validation (perhaps your application already handles all of
        this), then MySQL gives you the option of leaving strict mode
        disabled. However, if you do require it, strict mode can provide
        such validation.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-what-default-mode"></a><a name="idm139899453216496"></a><p><b>A.3.7.</b></p></td><td align="left" valign="top"><p>
        What is the default server SQL mode when MySQL 8.0
        is installed?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The default SQL mode in MySQL 8.0 includes these modes:
        <a class="link" href="server-administration.html#sqlmode_only_full_group_by"><code class="literal">ONLY_FULL_GROUP_BY</code></a>,
        <a class="link" href="server-administration.html#sqlmode_strict_trans_tables"><code class="literal">STRICT_TRANS_TABLES</code></a>,
        <a class="link" href="server-administration.html#sqlmode_no_zero_in_date"><code class="literal">NO_ZERO_IN_DATE</code></a>,
        <a class="link" href="server-administration.html#sqlmode_no_zero_date"><code class="literal">NO_ZERO_DATE</code></a>,
        <a class="link" href="server-administration.html#sqlmode_error_for_division_by_zero"><code class="literal">ERROR_FOR_DIVISION_BY_ZERO</code></a>,
        and <a class="link" href="server-administration.html#sqlmode_no_engine_substitution"><code class="literal">NO_ENGINE_SUBSTITUTION</code></a>.
      </p><p>
        For information about all available modes and default MySQL
        behavior, see <a class="xref" href="server-administration.html#sql-mode" title="5.1.8 Server SQL Modes">Section 5.1.8, “Server SQL Modes”</a>.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-stored-procs"></a>A.4 MySQL 8.0 FAQ: Stored Procedures and Functions</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899453205024"></a><dl><dt>A.4.1. <a href="faqs.html#faq-mysql-have-procedures-functions">
        Does MySQL 8.0 support stored procedures and
        functions?
      </a></dt><dt>A.4.2. <a href="faqs.html#faq-mysql-where-procedures-functions-docs">
        Where can I find documentation for MySQL stored procedures and
        stored functions?
      </a></dt><dt>A.4.3. <a href="faqs.html#faq-mysql-where-procedures-forum">
        Is there a discussion forum for MySQL stored procedures?
      </a></dt><dt>A.4.4. <a href="faqs.html#faq-mysql-where-ansi-2003-spec">
        Where can I find the ANSI SQL 2003 specification for stored
        procedures?
      </a></dt><dt>A.4.5. <a href="faqs.html#faq-mysql-how-manage-routines">
        How do you manage stored routines?
      </a></dt><dt>A.4.6. <a href="faqs.html#faq-mysql-how-view-procedures-functions">
        Is there a way to view all stored procedures and stored
        functions in a given database?
      </a></dt><dt>A.4.7. <a href="faqs.html#faq-mysql-where-procedures-stored">
        Where are stored procedures stored?
      </a></dt><dt>A.4.8. <a href="faqs.html#faq-mysql-how-group-procedures-functions">
        Is it possible to group stored procedures or stored functions
        into packages?
      </a></dt><dt>A.4.9. <a href="faqs.html#faq-mysql-can-procedure-call-procedure">
        Can a stored procedure call another stored procedure?
      </a></dt><dt>A.4.10. <a href="faqs.html#faq-mysql-can-procedure-call-trigger">
        Can a stored procedure call a trigger?
      </a></dt><dt>A.4.11. <a href="faqs.html#faq-mysql-can-procedure-access-table">
        Can a stored procedure access tables?
      </a></dt><dt>A.4.12. <a href="faqs.html#faq-mysql-can-procedure-raise-error">
        Do stored procedures have a statement for raising application
        errors?
      </a></dt><dt>A.4.13. <a href="faqs.html#faq-mysql-have-exceptions">
        Do stored procedures provide exception handling?
      </a></dt><dt>A.4.14. <a href="faqs.html#faq-mysql-can-routine-results">
        Can MySQL 8.0 stored routines return result sets?
      </a></dt><dt>A.4.15. <a href="faqs.html#faq-mysql-have-with-recompile">
        Is WITH RECOMPILE supported for stored
        procedures?
      </a></dt><dt>A.4.16. <a href="faqs.html#faq-mysql-have-mod-plsql">
        Is there a MySQL equivalent to using
        mod_plsql as a gateway on Apache to talk
        directly to a stored procedure in the database?
      </a></dt><dt>A.4.17. <a href="faqs.html#faq-mysql-can-procedure-array">
        Can I pass an array as input to a stored procedure?
      </a></dt><dt>A.4.18. <a href="faqs.html#faq-mysql-can-pass-cursor-in">
        Can I pass a cursor as an IN parameter to a
        stored procedure?
      </a></dt><dt>A.4.19. <a href="faqs.html#faq-mysql-can-return-cursor-out">
        Can I return a cursor as an OUT parameter
        from a stored procedure?
      </a></dt><dt>A.4.20. <a href="faqs.html#faq-mysql-can-print-var-in-procedure">
        Can I print out a variable's value within a stored routine for
        debugging purposes?
      </a></dt><dt>A.4.21. <a href="faqs.html#faq-mysql-can-rollback-transaction-procedure">
        Can I commit or roll back transactions inside a stored
        procedure?
      </a></dt><dt>A.4.22. <a href="faqs.html#faq-mysql-can-procedures-replicatation">
        Do MySQL 8.0 stored procedures and functions work
        with replication?
      </a></dt><dt>A.4.23. <a href="faqs.html#faq-mysql-are-procedures-replicated">
        Are stored procedures and functions created on a master server
        replicated to a slave?
      </a></dt><dt>A.4.24. <a href="faqs.html#faq-mysql-how-procedures-replicated">
        How are actions that take place inside stored procedures and
        functions replicated?
      </a></dt><dt>A.4.25. <a href="faqs.html#faq-mysql-security-procedures-replication">
        Are there special security requirements for using stored
        procedures and functions together with replication?
      </a></dt><dt>A.4.26. <a href="faqs.html#faq-mysql-limitations-procedures-replication">
        What limitations exist for replicating stored procedure and
        function actions?
      </a></dt><dt>A.4.27. <a href="faqs.html#faq-mysql-limitations-pit-recovery">
        Do the preceding limitations affect the ability of MySQL to do
        point-in-time recovery?
      </a></dt><dt>A.4.28. <a href="faqs.html#faq-mysql-when-limitations-resolved">
        What is being done to correct the aforementioned limitations?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-procedures-functions"></a><a name="idm139899453204272"></a><p><b>A.4.1.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 support stored procedures and
        functions?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. MySQL 8.0 supports two types of stored
        routines, stored procedures and stored functions.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-procedures-functions-docs"></a><a name="idm139899453202080"></a><p><b>A.4.2.</b></p></td><td align="left" valign="top"><p>
        Where can I find documentation for MySQL stored procedures and
        stored functions?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        See <a class="xref" href="stored-programs-views.html#stored-routines" title="23.2 Using Stored Routines (Procedures and Functions)">Section 23.2, “Using Stored Routines (Procedures and Functions)”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-procedures-forum"></a><a name="idm139899453199376"></a><p><b>A.4.3.</b></p></td><td align="left" valign="top"><p>
        Is there a discussion forum for MySQL stored procedures?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. See
        <a class="ulink" href="http://forums.mysql.com/list.php?98" target="_top">http://forums.mysql.com/list.php?98</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-ansi-2003-spec"></a><a name="idm139899453196624"></a><p><b>A.4.4.</b></p></td><td align="left" valign="top"><p>
        Where can I find the ANSI SQL 2003 specification for stored
        procedures?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Unfortunately, the official specifications are not freely
        available (ANSI makes them available for purchase). However,
        there are books, such as <em class="citetitle">SQL-99 Complete,
        Really</em> by Peter Gulutzan and Trudy Pelzer, that
        provide a comprehensive overview of the standard, including
        coverage of stored procedures.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-manage-routines"></a><a name="idm139899453193856"></a><p><b>A.4.5.</b></p></td><td align="left" valign="top"><p>
        How do you manage stored routines?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        It is always good practice to use a clear naming scheme for your
        stored routines. You can manage stored procedures with
        <code class="literal">CREATE [FUNCTION|PROCEDURE]</code>, <code class="literal">ALTER
        [FUNCTION|PROCEDURE]</code>, <code class="literal">DROP
        [FUNCTION|PROCEDURE]</code>, and <code class="literal">SHOW CREATE
        [FUNCTION|PROCEDURE]</code>. You can obtain information about
        existing stored procedures using the
        <a class="link" href="information-schema.html#routines-table" title="24.20 The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">ROUTINES</code></a> table in the
        <code class="literal">INFORMATION_SCHEMA</code> database (see
        <a class="xref" href="information-schema.html#routines-table" title="24.20 The INFORMATION_SCHEMA ROUTINES Table">Section 24.20, “The INFORMATION_SCHEMA ROUTINES Table”</a>).
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-view-procedures-functions"></a><a name="idm139899453186176"></a><p><b>A.4.6.</b></p></td><td align="left" valign="top"><p>
        Is there a way to view all stored procedures and stored
        functions in a given database?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. For a database named <em class="replaceable"><code>dbname</code></em>, use
        this query on the
        <a class="link" href="information-schema.html#routines-table" title="24.20 The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">INFORMATION_SCHEMA.ROUTINES</code></a> table:
      </p><pre data-lang="sql" class="programlisting">
SELECT ROUTINE_TYPE, ROUTINE_NAME
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_SCHEMA='<em class="replaceable"><code>dbname</code></em>';
</pre><p>
        For more information, see <a class="xref" href="information-schema.html#routines-table" title="24.20 The INFORMATION_SCHEMA ROUTINES Table">Section 24.20, “The INFORMATION_SCHEMA ROUTINES Table”</a>.
      </p><p>
        The body of a stored routine can be viewed using
        <a class="link" href="sql-syntax.html#show-create-function" title="13.7.6.8 SHOW CREATE FUNCTION Syntax"><code class="literal">SHOW CREATE FUNCTION</code></a> (for a
        stored function) or <a class="link" href="sql-syntax.html#show-create-procedure" title="13.7.6.9 SHOW CREATE PROCEDURE Syntax"><code class="literal">SHOW CREATE
        PROCEDURE</code></a> (for a stored procedure). See
        <a class="xref" href="sql-syntax.html#show-create-procedure" title="13.7.6.9 SHOW CREATE PROCEDURE Syntax">Section 13.7.6.9, “SHOW CREATE PROCEDURE Syntax”</a>, for more information.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-procedures-stored"></a><a name="idm139899453176192"></a><p><b>A.4.7.</b></p></td><td align="left" valign="top"><p>
        Where are stored procedures stored?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Stored procedures are stored in the
        <code class="literal">mysql.routines</code> and
        <code class="literal">mysql.parameters</code> tables, which are part of
        the data dictionary. You cannot access these tables directly.
        Instead, query the <code class="literal">INFORMATION_SCHEMA</code>
        <a class="link" href="information-schema.html#routines-table" title="24.20 The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">ROUTINES</code></a> and
        <a class="link" href="information-schema.html#parameters-table" title="24.13 The INFORMATION_SCHEMA PARAMETERS Table"><code class="literal">PARAMETERS</code></a> tables. See
        <a class="xref" href="information-schema.html#routines-table" title="24.20 The INFORMATION_SCHEMA ROUTINES Table">Section 24.20, “The INFORMATION_SCHEMA ROUTINES Table”</a>, and
        <a class="xref" href="information-schema.html#parameters-table" title="24.13 The INFORMATION_SCHEMA PARAMETERS Table">Section 24.13, “The INFORMATION_SCHEMA PARAMETERS Table”</a>.
      </p><p>
        You can also use <a class="link" href="sql-syntax.html#show-create-function" title="13.7.6.8 SHOW CREATE FUNCTION Syntax"><code class="literal">SHOW CREATE
        FUNCTION</code></a> to obtain information about stored functions,
        and <a class="link" href="sql-syntax.html#show-create-procedure" title="13.7.6.9 SHOW CREATE PROCEDURE Syntax"><code class="literal">SHOW CREATE PROCEDURE</code></a> to
        obtain information about stored procedures. See
        <a class="xref" href="sql-syntax.html#show-create-procedure" title="13.7.6.9 SHOW CREATE PROCEDURE Syntax">Section 13.7.6.9, “SHOW CREATE PROCEDURE Syntax”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-group-procedures-functions"></a><a name="idm139899453164464"></a><p><b>A.4.8.</b></p></td><td align="left" valign="top"><p>
        Is it possible to group stored procedures or stored functions
        into packages?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No. This is not supported in MySQL 8.0.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-procedure-call-procedure"></a><a name="idm139899453162448"></a><p><b>A.4.9.</b></p></td><td align="left" valign="top"><p>
        Can a stored procedure call another stored procedure?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-procedure-call-trigger"></a><a name="idm139899453160480"></a><p><b>A.4.10.</b></p></td><td align="left" valign="top"><p>
        Can a stored procedure call a trigger?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        A stored procedure can execute an SQL statement, such as an
        <a class="link" href="sql-syntax.html#update" title="13.2.12 UPDATE Syntax"><code class="literal">UPDATE</code></a>, that causes a trigger to
        activate.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-procedure-access-table"></a><a name="idm139899453157200"></a><p><b>A.4.11.</b></p></td><td align="left" valign="top"><p>
        Can a stored procedure access tables?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. A stored procedure can access one or more tables as
        required.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-procedure-raise-error"></a><a name="idm139899453155168"></a><p><b>A.4.12.</b></p></td><td align="left" valign="top"><p>
        Do stored procedures have a statement for raising application
        errors?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. MySQL 8.0 implements the SQL standard
        <code class="literal">SIGNAL</code> and <code class="literal">RESIGNAL</code>
        statements. See <a class="xref" href="sql-syntax.html#condition-handling" title="13.6.7 Condition Handling">Section 13.6.7, “Condition Handling”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-exceptions"></a><a name="idm139899453150992"></a><p><b>A.4.13.</b></p></td><td align="left" valign="top"><p>
        Do stored procedures provide exception handling?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        MySQL implements <a class="link" href="sql-syntax.html#handler" title="13.2.4 HANDLER Syntax"><code class="literal">HANDLER</code></a>
        definitions according to the SQL standard. See
        <a class="xref" href="sql-syntax.html#declare-handler" title="13.6.7.2 DECLARE ... HANDLER Syntax">Section 13.6.7.2, “DECLARE ... HANDLER Syntax”</a>, for details.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-routine-results"></a><a name="idm139899453147056"></a><p><b>A.4.14.</b></p></td><td align="left" valign="top"><p>
        Can MySQL 8.0 stored routines return result sets?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        <span class="emphasis"><em>Stored procedures</em></span> can, but stored functions
        cannot. If you perform an ordinary
        <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> inside a stored procedure,
        the result set is returned directly to the client. You need to
        use the MySQL 4.1 (or higher) client/server protocol for this to
        work. This means that, for example, in PHP, you need to use the
        <code class="literal">mysqli</code> extension rather than the old
        <code class="literal">mysql</code> extension.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-with-recompile"></a><a name="idm139899453141616"></a><p><b>A.4.15.</b></p></td><td align="left" valign="top"><p>
        Is <code class="literal">WITH RECOMPILE</code> supported for stored
        procedures?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Not in MySQL 8.0.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-mod-plsql"></a><a name="idm139899453138976"></a><p><b>A.4.16.</b></p></td><td align="left" valign="top"><p>
        Is there a MySQL equivalent to using
        <code class="literal">mod_plsql</code> as a gateway on Apache to talk
        directly to a stored procedure in the database?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        There is no equivalent in MySQL 8.0.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-procedure-array"></a><a name="idm139899453136128"></a><p><b>A.4.17.</b></p></td><td align="left" valign="top"><p>
        Can I pass an array as input to a stored procedure?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Not in MySQL 8.0.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-pass-cursor-in"></a><a name="idm139899453134144"></a><p><b>A.4.18.</b></p></td><td align="left" valign="top"><p>
        Can I pass a cursor as an <code class="literal">IN</code> parameter to a
        stored procedure?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        In MySQL 8.0, cursors are available inside stored
        procedures only.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-return-cursor-out"></a><a name="idm139899453131408"></a><p><b>A.4.19.</b></p></td><td align="left" valign="top"><p>
        Can I return a cursor as an <code class="literal">OUT</code> parameter
        from a stored procedure?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        In MySQL 8.0, cursors are available inside stored
        procedures only. However, if you do not open a cursor on a
        <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a>, the result will be sent
        directly to the client. You can also <code class="literal">SELECT
        INTO</code> variables. See <a class="xref" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax">Section 13.2.10, “SELECT Syntax”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-print-var-in-procedure"></a><a name="idm139899453125904"></a><p><b>A.4.20.</b></p></td><td align="left" valign="top"><p>
        Can I print out a variable's value within a stored routine for
        debugging purposes?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes, you can do this in a <span class="emphasis"><em>stored procedure</em></span>,
        but not in a stored function. If you perform an ordinary
        <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> inside a stored procedure,
        the result set is returned directly to the client. You will need
        to use the MySQL 4.1 (or above) client/server protocol for this
        to work. This means that, for example, in PHP, you need to use
        the <code class="literal">mysqli</code> extension rather than the old
        <code class="literal">mysql</code> extension.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-rollback-transaction-procedure"></a><a name="idm139899453120320"></a><p><b>A.4.21.</b></p></td><td align="left" valign="top"><p>
        Can I commit or roll back transactions inside a stored
        procedure?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. However, you cannot perform transactional operations within
        a stored function.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-procedures-replicatation"></a><a name="idm139899453118240"></a><p><b>A.4.22.</b></p></td><td align="left" valign="top"><p>
        Do MySQL 8.0 stored procedures and functions work
        with replication?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes, standard actions carried out in stored procedures and
        functions are replicated from a master MySQL server to a slave
        server. There are a few limitations that are described in detail
        in <a class="xref" href="stored-programs-views.html#stored-programs-logging" title="23.7 Binary Logging of Stored Programs">Section 23.7, “Binary Logging of Stored Programs”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-are-procedures-replicated"></a><a name="idm139899453115360"></a><p><b>A.4.23.</b></p></td><td align="left" valign="top"><p>
        Are stored procedures and functions created on a master server
        replicated to a slave?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes, creation of stored procedures and functions carried out
        through normal DDL statements on a master server are replicated
        to a slave, so the objects will exist on both servers.
        <code class="literal">ALTER</code> and <code class="literal">DROP</code> statements
        for stored procedures and functions are also replicated.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-procedures-replicated"></a><a name="idm139899453111648"></a><p><b>A.4.24.</b></p></td><td align="left" valign="top"><p>
        How are actions that take place inside stored procedures and
        functions replicated?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        MySQL records each DML event that occurs in a stored procedure
        and replicates those individual actions to a slave server. The
        actual calls made to execute stored procedures are not
        replicated.
      </p><p>
        Stored functions that change data are logged as function
        invocations, not as the DML events that occur inside each
        function.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-security-procedures-replication"></a><a name="idm139899453108800"></a><p><b>A.4.25.</b></p></td><td align="left" valign="top"><p>
        Are there special security requirements for using stored
        procedures and functions together with replication?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. Because a slave server has authority to execute any
        statement read from a master's binary log, special security
        constraints exist for using stored functions with replication.
        If replication or binary logging in general (for the purpose of
        point-in-time recovery) is active, then MySQL DBAs have two
        security options open to them:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
              Any user wishing to create stored functions must be
              granted the <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a>
              privilege.
            </p></li><li class="listitem"><p>
              Alternatively, a DBA can set the
              <a class="link" href="server-administration.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators</code></a>
              system variable to 1, which enables anyone with the
              standard <a class="link" href="security.html#priv_create-routine"><code class="literal">CREATE ROUTINE</code></a>
              privilege to create stored functions.
</p></li></ol>
</div>
<p>
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-limitations-procedures-replication"></a><a name="idm139899453099744"></a><p><b>A.4.26.</b></p></td><td align="left" valign="top"><p>
        What limitations exist for replicating stored procedure and
        function actions?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Nondeterministic (random) or time-based actions embedded in
        stored procedures may not replicate properly. By their very
        nature, randomly produced results are not predictable and cannot
        be exactly reproduced, and therefore, random actions replicated
        to a slave will not mirror those performed on a master.
        Declaring stored functions to be
        <code class="literal">DETERMINISTIC</code> or setting the
        <a class="link" href="server-administration.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators</code></a>
        system variable to 0 will not allow random-valued operations to
        be invoked.
      </p><p>
        In addition, time-based actions cannot be reproduced on a slave
        because the timing of such actions in a stored procedure is not
        reproducible through the binary log used for replication. It
        records only DML events and does not factor in timing
        constraints.
      </p><p>
        Finally, nontransactional tables for which errors occur during
        large DML actions (such as bulk inserts) may experience
        replication issues in that a master may be partially updated
        from DML activity, but no updates are done to the slave because
        of the errors that occurred. A workaround is for a function's
        DML actions to be carried out with the <code class="literal">IGNORE</code>
        keyword so that updates on the master that cause errors are
        ignored and updates that do not cause errors are replicated to
        the slave.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-limitations-pit-recovery"></a><a name="idm139899453092848"></a><p><b>A.4.27.</b></p></td><td align="left" valign="top"><p>
        Do the preceding limitations affect the ability of MySQL to do
        point-in-time recovery?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The same limitations that affect replication do affect
        point-in-time recovery.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-when-limitations-resolved"></a><a name="idm139899453090688"></a><p><b>A.4.28.</b></p></td><td align="left" valign="top"><p>
        What is being done to correct the aforementioned limitations?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        You can choose either statement-based replication or row-based
        replication. The original replication implementation is based on
        statement-based binary logging. Row-based binary logging
        resolves the limitations mentioned earlier.
      </p><p>
        <span class="firstterm">Mixed</span> replication is also
        available (by starting the server with
        <a class="link" href="server-administration.html#option_mysqld_binlog-format"><code class="option">--binlog-format=mixed</code></a>). This
        hybrid form of replication <span class="quote">“<span class="quote">knows</span>”</span> whether
        statement-level replication can safely be used, or row-level
        replication is required.
      </p><p>
        For additional information, see
        <a class="xref" href="replication.html#replication-formats" title="17.2.1 Replication Formats">Section 17.2.1, “Replication Formats”</a>.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-triggers"></a>A.5 MySQL 8.0 FAQ: Triggers</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899453083920"></a><dl><dt>A.5.1. <a href="faqs.html#faq-mysql-where-triggers-docs">
        Where can I find the documentation for MySQL 8.0
        triggers?
      </a></dt><dt>A.5.2. <a href="faqs.html#faq-mysql-where-triggers-forum">
        Is there a discussion forum for MySQL Triggers?
      </a></dt><dt>A.5.3. <a href="faqs.html#faq-mysql-have-trigger-levels">
        Does MySQL 8.0 have statement-level or row-level
        triggers?
      </a></dt><dt>A.5.4. <a href="faqs.html#faq-mysql-have-trigger-defaults">
        Are there any default triggers?
      </a></dt><dt>A.5.5. <a href="faqs.html#faq-mysql-how-triggers-managed">
        How are triggers managed in MySQL?
      </a></dt><dt>A.5.6. <a href="faqs.html#faq-mysql-can-view-all-triggers">
        Is there a way to view all triggers in a given database?
      </a></dt><dt>A.5.7. <a href="faqs.html#faq-mysql-how-triggers-stored">
        Where are triggers stored?
      </a></dt><dt>A.5.8. <a href="faqs.html#faq-mysql-can-trigger-procedure">
        Can a trigger call a stored procedure?
      </a></dt><dt>A.5.9. <a href="faqs.html#faq-mysql-can-triggers-tables">
        Can triggers access tables?
      </a></dt><dt>A.5.10. <a href="faqs.html#faq-mysql-can-triggers-same-events">
        Can a table have multiple triggers with the same trigger event
        and action time?
      </a></dt><dt>A.5.11. <a href="faqs.html#faq-mysql-can-triggers-udf">
        Can triggers call an external application through a UDF?
      </a></dt><dt>A.5.12. <a href="faqs.html#faq-mysql-can-triggers-remote">
        Is it possible for a trigger to update tables on a remote
        server?
      </a></dt><dt>A.5.13. <a href="faqs.html#faq-mysql-can-triggers-replication">
        Do triggers work with replication?
      </a></dt><dt>A.5.14. <a href="faqs.html#faq-mysql-how-triggers-master-slave">
        How are actions carried out through triggers on a master
        replicated to a slave?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-triggers-docs"></a><a name="idm139899453083104"></a><p><b>A.5.1.</b></p></td><td align="left" valign="top"><p>
        Where can I find the documentation for MySQL 8.0
        triggers?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        See <a class="xref" href="stored-programs-views.html#triggers" title="23.3 Using Triggers">Section 23.3, “Using Triggers”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-triggers-forum"></a><a name="idm139899453080448"></a><p><b>A.5.2.</b></p></td><td align="left" valign="top"><p>
        Is there a discussion forum for MySQL Triggers?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. It is available at <a class="ulink" href="http://forums.mysql.com/list.php?99" target="_top">http://forums.mysql.com/list.php?99</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-trigger-levels"></a><a name="idm139899453077824"></a><p><b>A.5.3.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 have statement-level or row-level
        triggers?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        In MySQL 8.0, all triggers are <code class="literal">FOR EACH
        ROW</code>; that is, the trigger is activated for each row
        that is inserted, updated, or deleted. MySQL 8.0
        does not support triggers using <code class="literal">FOR EACH
        STATEMENT</code>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-trigger-defaults"></a><a name="idm139899453074160"></a><p><b>A.5.4.</b></p></td><td align="left" valign="top"><p>
        Are there any default triggers?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Not explicitly. MySQL does have specific special behavior for
        some <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> columns, as well
        as for columns which are defined using
        <code class="literal">AUTO_INCREMENT</code>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-triggers-managed"></a><a name="idm139899453070128"></a><p><b>A.5.5.</b></p></td><td align="left" valign="top"><p>
        How are triggers managed in MySQL?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        In MySQL 8.0, triggers can be created using the
        <a class="link" href="sql-syntax.html#create-trigger" title="13.1.20 CREATE TRIGGER Syntax"><code class="literal">CREATE TRIGGER</code></a> statement, and
        dropped using <a class="link" href="sql-syntax.html#drop-trigger" title="13.1.31 DROP TRIGGER Syntax"><code class="literal">DROP TRIGGER</code></a>. See
        <a class="xref" href="sql-syntax.html#create-trigger" title="13.1.20 CREATE TRIGGER Syntax">Section 13.1.20, “CREATE TRIGGER Syntax”</a>, and
        <a class="xref" href="sql-syntax.html#drop-trigger" title="13.1.31 DROP TRIGGER Syntax">Section 13.1.31, “DROP TRIGGER Syntax”</a>, for more about these statements.
      </p><p>
        Information about triggers can be obtained by querying the
        <a class="link" href="information-schema.html#triggers-table" title="24.30 The INFORMATION_SCHEMA TRIGGERS Table"><code class="literal">INFORMATION_SCHEMA.TRIGGERS</code></a> table.
        See <a class="xref" href="information-schema.html#triggers-table" title="24.30 The INFORMATION_SCHEMA TRIGGERS Table">Section 24.30, “The INFORMATION_SCHEMA TRIGGERS Table”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-view-all-triggers"></a><a name="idm139899453061872"></a><p><b>A.5.6.</b></p></td><td align="left" valign="top"><p>
        Is there a way to view all triggers in a given database?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. You can obtain a listing of all triggers defined on
        database <code class="literal">dbname</code> using a query on the
        <a class="link" href="information-schema.html#triggers-table" title="24.30 The INFORMATION_SCHEMA TRIGGERS Table"><code class="literal">INFORMATION_SCHEMA.TRIGGERS</code></a> table
        such as the one shown here:
      </p><pre data-lang="sql" class="programlisting">
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT
    FROM INFORMATION_SCHEMA.TRIGGERS
    WHERE TRIGGER_SCHEMA='<em class="replaceable"><code>dbname</code></em>';
</pre><p>
        For more information about this table, see
        <a class="xref" href="information-schema.html#triggers-table" title="24.30 The INFORMATION_SCHEMA TRIGGERS Table">Section 24.30, “The INFORMATION_SCHEMA TRIGGERS Table”</a>.
      </p><p>
        You can also use the <a class="link" href="sql-syntax.html#show-triggers" title="13.7.6.38 SHOW TRIGGERS Syntax"><code class="literal">SHOW
        TRIGGERS</code></a> statement, which is specific to MySQL. See
        <a class="xref" href="sql-syntax.html#show-triggers" title="13.7.6.38 SHOW TRIGGERS Syntax">Section 13.7.6.38, “SHOW TRIGGERS Syntax”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-triggers-stored"></a><a name="idm139899453052832"></a><p><b>A.5.7.</b></p></td><td align="left" valign="top"><p>
        Where are triggers stored?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Triggers are stored in the <code class="literal">mysql.triggers</code>
        system table, which is part of the data dictionary.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-trigger-procedure"></a><a name="idm139899453050032"></a><p><b>A.5.8.</b></p></td><td align="left" valign="top"><p>
        Can a trigger call a stored procedure?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-triggers-tables"></a><a name="idm139899453048080"></a><p><b>A.5.9.</b></p></td><td align="left" valign="top"><p>
        Can triggers access tables?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        A trigger can access both old and new data in its own table. A
        trigger can also affect other tables, but it is not permitted to
        modify a table that is already being used (for reading or
        writing) by the statement that invoked the function or trigger.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-triggers-same-events"></a><a name="idm139899453045792"></a><p><b>A.5.10.</b></p></td><td align="left" valign="top"><p>
        Can a table have multiple triggers with the same trigger event
        and action time?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        In MySQL 8.0, it is possible to define multiple
        triggers for a given table that have the same trigger event and
        action time. For example, you can have two <code class="literal">BEFORE
        UPDATE</code> triggers for a table. By default, triggers that
        have the same trigger event and action time activate in the
        order they were created. To affect trigger order, specify a
        clause after <code class="literal">FOR EACH ROW</code> that indicates
        <code class="literal">FOLLOWS</code> or <code class="literal">PRECEDES</code> and
        the name of an existing trigger that also has the same trigger
        event and action time. With <code class="literal">FOLLOWS</code>, the new
        trigger activates after the existing trigger. With
        <code class="literal">PRECEDES</code>, the new trigger activates before
        the existing trigger.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-triggers-udf"></a><a name="idm139899453038896"></a><p><b>A.5.11.</b></p></td><td align="left" valign="top"><p>
        Can triggers call an external application through a UDF?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. For example, a trigger could invoke the
        <code class="literal">sys_exec()</code> UDF.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-triggers-remote"></a><a name="idm139899453036144"></a><p><b>A.5.12.</b></p></td><td align="left" valign="top"><p>
        Is it possible for a trigger to update tables on a remote
        server?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. A table on a remote server could be updated using the
        <code class="literal">FEDERATED</code> storage engine. (See
        <a class="xref" href="storage-engines.html#federated-storage-engine" title="16.8 The FEDERATED Storage Engine">Section 16.8, “The FEDERATED Storage Engine”</a>).
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-triggers-replication"></a><a name="idm139899453032640"></a><p><b>A.5.13.</b></p></td><td align="left" valign="top"><p>
        Do triggers work with replication?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. However, the way in which they work depends whether you are
        using MySQL's <span class="quote">“<span class="quote">classic</span>”</span> statement-based or
        row-based replication format.
      </p><p>
        When using statement-based replication, triggers on the slave
        are executed by statements that are executed on the master (and
        replicated to the slave).
      </p><p>
        When using row-based replication, triggers are not executed on
        the slave due to statements that were run on the master and then
        replicated to the slave. Instead, when using row-based
        replication, the changes caused by executing the trigger on the
        master are applied on the slave.
      </p><p>
        For more information, see
        <a class="xref" href="replication.html#replication-features-triggers" title="17.4.1.36 Replication and Triggers">Section 17.4.1.36, “Replication and Triggers”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-triggers-master-slave"></a><a name="idm139899453027536"></a><p><b>A.5.14.</b></p></td><td align="left" valign="top"><p>
        How are actions carried out through triggers on a master
        replicated to a slave?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Again, this depends on whether you are using statement-based or
        row-based replication.
      </p><p><b>Statement-based replication. </b>
          First, the triggers that exist on a master must be re-created
          on the slave server. Once this is done, the replication flow
          works as any other standard DML statement that participates in
          replication. For example, consider a table
          <code class="literal">EMP</code> that has an <code class="literal">AFTER</code>
          insert trigger, which exists on a master MySQL server. The
          same <code class="literal">EMP</code> table and <code class="literal">AFTER</code>
          insert trigger exist on the slave server as well. The
          replication flow would be:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
            An <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statement is made
            to <code class="literal">EMP</code>.
          </p></li><li class="listitem"><p>
            The <code class="literal">AFTER</code> trigger on
            <code class="literal">EMP</code> activates.
          </p></li><li class="listitem"><p>
            The <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statement is
            written to the binary log.
          </p></li><li class="listitem"><p>
            The replication slave picks up the
            <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statement to
            <code class="literal">EMP</code> and executes it.
          </p></li><li class="listitem"><p>
            The <code class="literal">AFTER</code> trigger on
            <code class="literal">EMP</code> that exists on the slave activates.
</p></li></ol>
</div>
<p><b>Row-based replication. </b>
          When you use row-based replication, the changes caused by
          executing the trigger on the master are applied on the slave.
          However, the triggers themselves are not actually executed on
          the slave under row-based replication. This is because, if
          both the master and the slave applied the changes from the
          master and, in addition, the trigger causing these changes
          were applied on the slave, the changes would in effect be
          applied twice on the slave, leading to different data on the
          master and the slave.
        </p><p>
        In most cases, the outcome is the same for both row-based and
        statement-based replication. However, if you use different
        triggers on the master and slave, you cannot use row-based
        replication. (This is because the row-based format replicates
        the changes made by triggers executing on the master to the
        slaves, rather than the statements that caused the triggers to
        execute, and the corresponding triggers on the slave are not
        executed.) Instead, any statements causing such triggers to be
        executed must be replicated using statement-based replication.
      </p><p>
        For more information, see
        <a class="xref" href="replication.html#replication-features-triggers" title="17.4.1.36 Replication and Triggers">Section 17.4.1.36, “Replication and Triggers”</a>.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-views"></a>A.6 MySQL 8.0 FAQ: Views</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899453003136"></a><dl><dt>A.6.1. <a href="faqs.html#faq-mysql-where-docs-views">
        Where can I find documentation covering MySQL Views?
      </a></dt><dt>A.6.2. <a href="faqs.html#faq-mysql-where-views-forum">
        Is there a discussion forum for MySQL Views?
      </a></dt><dt>A.6.3. <a href="faqs.html#faq-mysql-where-view-dropped-table">
        What happens to a view if an underlying table is dropped or
        renamed?
      </a></dt><dt>A.6.4. <a href="faqs.html#faq-mysql-have-table-snapshots">
        Does MySQL 8.0 have table snapshots?
      </a></dt><dt>A.6.5. <a href="faqs.html#faq-mysql-have-materialized-views">
        Does MySQL 8.0 have materialized views?
      </a></dt><dt>A.6.6. <a href="faqs.html#faq-mysql-can-insert-joins-views">
        Can you insert into views that are based on joins?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-docs-views"></a><a name="idm139899453002368"></a><p><b>A.6.1.</b></p></td><td align="left" valign="top"><p>
        Where can I find documentation covering MySQL Views?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        See <a class="xref" href="stored-programs-views.html#views" title="23.5 Using Views">Section 23.5, “Using Views”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-views-forum"></a><a name="idm139899452999600"></a><p><b>A.6.2.</b></p></td><td align="left" valign="top"><p>
        Is there a discussion forum for MySQL Views?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. See
        <a class="ulink" href="http://forums.mysql.com/list.php?100" target="_top">http://forums.mysql.com/list.php?100</a>
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-view-dropped-table"></a><a name="idm139899452996832"></a><p><b>A.6.3.</b></p></td><td align="left" valign="top"><p>
        What happens to a view if an underlying table is dropped or
        renamed?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        After a view has been created, it is possible to drop or alter a
        table or view to which the definition refers. To check a view
        definition for problems of this kind, use the
        <a class="link" href="sql-syntax.html#check-table" title="13.7.3.2 CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a> statement. (See
        <a class="xref" href="sql-syntax.html#check-table" title="13.7.3.2 CHECK TABLE Syntax">Section 13.7.3.2, “CHECK TABLE Syntax”</a>.)
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-table-snapshots"></a><a name="idm139899452992720"></a><p><b>A.6.4.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 have table snapshots?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-materialized-views"></a><a name="idm139899452990704"></a><p><b>A.6.5.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 have materialized views?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-insert-joins-views"></a><a name="idm139899452988752"></a><p><b>A.6.6.</b></p></td><td align="left" valign="top"><p>
        Can you insert into views that are based on joins?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        It is possible, provided that your
        <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> statement has a column
        list that makes it clear there is only one table involved.
      </p><p>
        You <span class="emphasis"><em>cannot</em></span> insert into multiple tables with
        a single insert on a view.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-information-schema"></a>A.7 MySQL 8.0 FAQ: INFORMATION_SCHEMA</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899452983728"></a><dl><dt>A.7.1. <a href="faqs.html#faq-mysql-where-docs-information-schema">
        Where can I find documentation for the MySQL
        INFORMATION_SCHEMA database?
      </a></dt><dt>A.7.2. <a href="faqs.html#faq-mysql-where-forum-information-schema">
        Is there a discussion forum for
        INFORMATION_SCHEMA?
      </a></dt><dt>A.7.3. <a href="faqs.html#faq-mysql-where-ansi-information-schema">
        Where can I find the ANSI SQL 2003 specification for
        INFORMATION_SCHEMA?
      </a></dt><dt>A.7.4. <a href="faqs.html#faq-mysql-compare-oracle-data-dir-info-schema">
        What is the difference between the Oracle Data Dictionary and
        MySQL INFORMATION_SCHEMA?
      </a></dt><dt>A.7.5. <a href="faqs.html#faq-mysql-can-modify-information-schema">
        Can I add to or otherwise modify the tables found in the
        INFORMATION_SCHEMA database?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-docs-information-schema"></a><a name="idm139899452982912"></a><p><b>A.7.1.</b></p></td><td align="left" valign="top"><p>
        Where can I find documentation for the MySQL
        <code class="literal">INFORMATION_SCHEMA</code> database?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        See <a class="xref" href="information-schema.html" title="Chapter 24 INFORMATION_SCHEMA Tables">Chapter 24, <i>INFORMATION_SCHEMA Tables</i></a>
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-forum-information-schema"></a><a name="idm139899452979536"></a><p><b>A.7.2.</b></p></td><td align="left" valign="top"><p>
        Is there a discussion forum for
        <code class="literal">INFORMATION_SCHEMA</code>?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        See
        <a class="ulink" href="http://forums.mysql.com/list.php?101" target="_top">http://forums.mysql.com/list.php?101</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-ansi-information-schema"></a><a name="idm139899452976160"></a><p><b>A.7.3.</b></p></td><td align="left" valign="top"><p>
        Where can I find the ANSI SQL 2003 specification for
        <code class="literal">INFORMATION_SCHEMA</code>?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Unfortunately, the official specifications are not freely
        available. (ANSI makes them available for purchase.) However,
        there are books available, such as <em class="citetitle">SQL-99 Complete,
        Really</em> by Peter Gulutzan and Trudy Pelzer, that
        provide a comprehensive overview of the standard, including
        <code class="literal">INFORMATION_SCHEMA</code>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-compare-oracle-data-dir-info-schema"></a><a name="idm139899452971920"></a><p><b>A.7.4.</b></p></td><td align="left" valign="top"><p>
        What is the difference between the Oracle Data Dictionary and
        MySQL <code class="literal">INFORMATION_SCHEMA</code>?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Both Oracle and MySQL provide metadata in tables. However,
        Oracle and MySQL use different table names and column names. The
        MySQL implementation is more similar to those found in DB2 and
        SQL Server, which also support
        <code class="literal">INFORMATION_SCHEMA</code> as defined in the SQL
        standard.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-can-modify-information-schema"></a><a name="idm139899452968224"></a><p><b>A.7.5.</b></p></td><td align="left" valign="top"><p>
        Can I add to or otherwise modify the tables found in the
        <code class="literal">INFORMATION_SCHEMA</code> database?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No. Since applications may rely on a certain standard structure,
        this should not be modified. For this reason, <span class="emphasis"><em>we
        cannot support bugs or other issues which result from modifying
        <code class="literal">INFORMATION_SCHEMA</code> tables or data</em></span>.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-migration"></a>A.8 MySQL 8.0 FAQ: Migration</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899452963440"></a><dl><dt>A.8.1. <a href="faqs.html#faq-mysql-how-migration-mysql">
        Where can I find information on how to migrate from MySQL
        5.7 to MySQL 8.0?
      </a></dt><dt>A.8.2. <a href="faqs.html#faq-mysql-how-storage-engines-changed">
        How has storage engine (table type) support changed in MySQL
        8.0 from previous versions?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-migration-mysql"></a><a name="idm139899452962624"></a><p><b>A.8.1.</b></p></td><td align="left" valign="top"><p>
        Where can I find information on how to migrate from MySQL
        5.7 to MySQL 8.0?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        For detailed upgrade information, see
        <a class="xref" href="installing.html#upgrading" title="2.11.1 Upgrading MySQL">Section 2.11.1, “Upgrading MySQL”</a>. Do not skip a major version when
        upgrading, but rather complete the process in steps, upgrading
        from one major version to the next in each step. This may seem
        more complicated, but it will you save time and trouble. If you
        encounter problems during the upgrade, their origin will be
        easier to identify, either by you or, if you have a MySQL
        Enterprise subscription, by MySQL support.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-how-storage-engines-changed"></a><a name="idm139899452959424"></a><p><b>A.8.2.</b></p></td><td align="left" valign="top"><p>
        How has storage engine (table type) support changed in MySQL
        8.0 from previous versions?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Storage engine support has changed as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              Support for <code class="literal">ISAM</code> tables was removed in
              MySQL 5.0 and you should now use the
              <code class="literal">MyISAM</code> storage engine in place of
              <code class="literal">ISAM</code>. To convert a table
              <em class="replaceable"><code>tblname</code></em> from
              <code class="literal">ISAM</code> to <code class="literal">MyISAM</code>,
              simply issue a statement such as this one:
            </p><pre data-lang="sql" class="programlisting">ALTER TABLE <em class="replaceable"><code>tblname</code></em> ENGINE=MYISAM;</pre></li><li class="listitem"><p>
              Internal <code class="literal">RAID</code> for
              <code class="literal">MyISAM</code> tables was also removed in MySQL
              5.0. This was formerly used to allow large tables in file
              systems that did not support file sizes greater than 2GB.
              All modern file systems allow for larger tables; in
              addition, there are now other solutions such as
              <code class="literal">MERGE</code> tables and views.
            </p></li><li class="listitem"><p>
              The <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> column type now
              retains trailing spaces in all storage engines.
            </p></li><li class="listitem"><p>
              <code class="literal">MEMORY</code> tables (formerly known as
              <code class="literal">HEAP</code> tables) can also contain
              <a class="link" href="data-types.html#char" title="11.4.1 The CHAR and VARCHAR Types"><code class="literal">VARCHAR</code></a> columns.
</p></li></ul>
</div>
<p>
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-security"></a>A.9 MySQL 8.0 FAQ: Security</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899452941008"></a><dl><dt>A.9.1. <a href="faqs.html#faq-mysql-where-docs-security">
        Where can I find documentation that addresses security issues
        for MySQL?
      </a></dt><dt>A.9.2. <a href="faqs.html#faq-mysql-have-native-ssl">
        Does MySQL 8.0 have native support for SSL?
      </a></dt><dt>A.9.3. <a href="faqs.html#faq-mysql-is-ssl-available">
        Is SSL support built into MySQL binaries, or must I recompile
        the binary myself to enable it?
      </a></dt><dt>A.9.4. <a href="faqs.html#faq-mysql-have-builtin-ldap">
        Does MySQL 8.0 have built-in authentication against
        LDAP directories?
      </a></dt><dt>A.9.5. <a href="faqs.html#faq-mysql-have-builtin-rbac">
        Does MySQL 8.0 include support for Roles Based
        Access Control (RBAC)?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-mysql-where-docs-security"></a><a name="idm139899452940192"></a><p><b>A.9.1.</b></p></td><td align="left" valign="top"><p>
        Where can I find documentation that addresses security issues
        for MySQL?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The best place to start is <a class="xref" href="security.html" title="Chapter 6 Security">Chapter 6, <i>Security</i></a>.
      </p><p>
        Other portions of the MySQL Documentation which you may find
        useful with regard to specific security concerns include the
        following:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              <a class="xref" href="security.html#security-guidelines" title="6.1.1 Security Guidelines">Section 6.1.1, “Security Guidelines”</a>.
            </p></li><li class="listitem"><p>
              <a class="xref" href="security.html#security-against-attack" title="6.1.3 Making MySQL Secure Against Attackers">Section 6.1.3, “Making MySQL Secure Against Attackers”</a>.
            </p></li><li class="listitem"><p>
              <a class="xref" href="error-handling.html#resetting-permissions" title="B.5.3.2 How to Reset the Root Password">Section B.5.3.2, “How to Reset the Root Password”</a>.
            </p></li><li class="listitem"><p>
              <a class="xref" href="security.html#changing-mysql-user" title="6.1.5 How to Run MySQL as a Normal User">Section 6.1.5, “How to Run MySQL as a Normal User”</a>.
            </p></li><li class="listitem"><p>
              <a class="xref" href="extending-mysql.html#udf-security" title="28.4.2.6 UDF Security Precautions">Section 28.4.2.6, “UDF Security Precautions”</a>.
            </p></li><li class="listitem"><p>
              <a class="xref" href="security.html#security-options" title="6.1.4 Security-Related mysqld Options and Variables">Section 6.1.4, “Security-Related mysqld Options and Variables”</a>.
            </p></li><li class="listitem"><p>
              <a class="xref" href="security.html#load-data-local" title="6.1.6 Security Issues with LOAD DATA LOCAL">Section 6.1.6, “Security Issues with LOAD DATA LOCAL”</a>.
            </p></li><li class="listitem"><p>
              <a class="xref" href="installing.html#postinstallation" title="2.10 Postinstallation Setup and Testing">Section 2.10, “Postinstallation Setup and Testing”</a>.
            </p></li><li class="listitem"><p>
              <a class="xref" href="security.html#encrypted-connections" title="6.4 Using Encrypted Connections">Section 6.4, “Using Encrypted Connections”</a>.
</p></li></ul>
</div>
<p>
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-native-ssl"></a><a name="idm139899452922736"></a><p><b>A.9.2.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 have native support for SSL?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Most 8.0 binaries have support for SSL connections
        between the client and server. See
        <a class="xref" href="security.html#encrypted-connections" title="6.4 Using Encrypted Connections">Section 6.4, “Using Encrypted Connections”</a>.
      </p><p>
        You can also tunnel a connection using SSH, if (for example) the
        client application does not support SSL connections. For an
        example, see <a class="xref" href="security.html#windows-and-ssh" title="6.4.7 Connecting to MySQL Remotely from Windows with SSH">Section 6.4.7, “Connecting to MySQL Remotely from Windows with SSH”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-is-ssl-available"></a><a name="idm139899452918704"></a><p><b>A.9.3.</b></p></td><td align="left" valign="top"><p>
        Is SSL support built into MySQL binaries, or must I recompile
        the binary myself to enable it?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Most 8.0 binaries have SSL enabled for
        client/server connections that are secured, authenticated, or
        both. See <a class="xref" href="security.html#encrypted-connections" title="6.4 Using Encrypted Connections">Section 6.4, “Using Encrypted Connections”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-builtin-ldap"></a><a name="idm139899452915824"></a><p><b>A.9.4.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 have built-in authentication against
        LDAP directories?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The Enterprise edition includes a
        <a class="link" href="security.html#pam-pluggable-authentication" title="6.5.1.5 PAM Pluggable Authentication">PAM Authentication
        Plugin</a> that supports authentication against an LDAP
        directory.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-mysql-have-builtin-rbac"></a><a name="idm139899452912864"></a><p><b>A.9.5.</b></p></td><td align="left" valign="top"><p>
        Does MySQL 8.0 include support for Roles Based
        Access Control (RBAC)?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Not at this time.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-mysql-cluster"></a>A.10 MySQL 8.0 FAQ: MySQL Cluster</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139899452910128"></a><a class="indexterm" name="idm139899452908656"></a><a class="indexterm" name="idm139899452907200"></a><p>
      In the following section, we answer questions that are frequently
      asked about MySQL Cluster and the <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top"><code class="literal">NDB</code></a>
      storage engine.
</p>
<div class="qandaset">
<a name="idm139899452904048"></a><dl><dt>A.10.1. <a href="faqs.html#faq-cluster-which-versions-support-cluster">
        Which versions of the MySQL software support NDB Cluster? Do I
        have to compile from source?
      </a></dt><dt>A.10.2. <a href="faqs.html#faq-cluster-what-is-ndb">
        What do “NDB” and “NDBCLUSTER” mean?
      </a></dt><dt>A.10.3. <a href="faqs.html#faq-cluster-what-min-hosts-cluster">
        How many computers do I need to run an NDB Cluster, and why?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-cluster-which-versions-support-cluster"></a><a name="idm139899452903248"></a><p><b>A.10.1.</b></p></td><td align="left" valign="top"><p>
        Which versions of the MySQL software support NDB Cluster? Do I
        have to compile from source?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        NDB Cluster is not supported in MySQL Server 8.0
        releases; it is released as a separate product. You are strongly
        advised to use NDB Cluster 7.5 for any new deployments; if you
        are using an older version of NDB Cluster, we recommend that you
        upgrade to this version soon as possible. For an overview of
        improvements made in NDB Cluster 7.5, see
        <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-what-is-new-7-5.html" target="_top">What is New in NDB Cluster 7.5</a>.
      </p><p>
        NDB Cluster 7.6, based on MySQL Server 5.7 and version 7.6 of
        the <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top"><code class="literal">NDB</code></a> storage engine, is also now
        available as a Developer Preview release for evaluation and
        testing purposes. For information about new features and other
        important changes in this series, see
        <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-what-is-new-7-6.html" target="_top">What is New in NDB Cluster 7.6</a>.
      </p><p>
        For detailed information about deploying and using NDB Cluster,
        see <a class="ulink" href="http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html" target="_top">MySQL NDB Cluster 7.5 and NDB Cluster 7.6</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cluster-what-is-ndb"></a><a name="idm139899452896144"></a><p><b>A.10.2.</b></p></td><td align="left" valign="top"><p>
        What do <span class="quote">“<span class="quote">NDB</span>”</span> and <span class="quote">“<span class="quote">NDBCLUSTER</span>”</span> mean?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        <span class="quote">“<span class="quote">NDB</span>”</span> stands for
        <span class="quote">“<span class="quote"><span class="bold"><strong>N</strong></span>etwork
        <span class="bold"><strong>D</strong></span>ata<span class="bold"><strong>b</strong></span>ase</span>”</span>.
        <code class="literal">NDB</code> and <code class="literal">NDBCLUSTER</code> are
        both names for the storage engine that enables clustering
        support with MySQL. <code class="literal">NDB</code> is preferred, but
        either name is correct.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cluster-what-min-hosts-cluster"></a><a name="idm139899452888352"></a><p><b>A.10.3.</b></p></td><td align="left" valign="top"><p>
        How many computers do I need to run an NDB Cluster, and why?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        A minimum of three computers is required to run a viable
        cluster. However, the minimum <span class="emphasis"><em>recommended</em></span>
        number of computers in an NDB Cluster is four: one each to run
        the management and SQL nodes, and two computers to serve as data
        nodes. The purpose of the two data nodes is to provide
        redundancy; the management node must run on a separate machine
        to guarantee continued arbitration services in the event that
        one of the data nodes fails.
      </p><p>
        To provide increased throughput and high availability, you
        should use multiple SQL nodes (MySQL Servers connected to the
        cluster). It is also possible (although not strictly necessary)
        to run multiple management servers.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-cjk"></a>A.11 MySQL 8.0 FAQ: MySQL Chinese, Japanese, and Korean
Character Sets</h2>
</div>
</div>
</div>
<a class="indexterm" name="idm139899452883984"></a><a class="indexterm" name="idm139899452882592"></a><a class="indexterm" name="idm139899452881056"></a><a class="indexterm" name="idm139899452879520"></a><a class="indexterm" name="idm139899452878000"></a><a class="indexterm" name="idm139899452876464"></a><a class="indexterm" name="idm139899452874960"></a><a class="indexterm" name="idm139899452873888"></a><a class="indexterm" name="idm139899452872368"></a><a class="indexterm" name="idm139899452870832"></a><a class="indexterm" name="idm139899452869328"></a><a class="indexterm" name="idm139899452868256"></a><a class="indexterm" name="idm139899452866720"></a><a class="indexterm" name="idm139899452865216"></a><a class="indexterm" name="idm139899452863728"></a><a class="indexterm" name="idm139899452862208"></a><a class="indexterm" name="idm139899452860704"></a><a class="indexterm" name="idm139899452859632"></a><a class="indexterm" name="idm139899452858096"></a><a class="indexterm" name="idm139899452856592"></a><a class="indexterm" name="idm139899452855520"></a><a class="indexterm" name="idm139899452854000"></a><a class="indexterm" name="idm139899452852496"></a><a class="indexterm" name="idm139899452851408"></a><a class="indexterm" name="idm139899452849888"></a><a class="indexterm" name="idm139899452848384"></a><a class="indexterm" name="idm139899452846880"></a><a class="indexterm" name="idm139899452845360"></a><a class="indexterm" name="idm139899452843856"></a><a class="indexterm" name="idm139899452842336"></a><a class="indexterm" name="idm139899452840816"></a><a class="indexterm" name="idm139899452839312"></a><a class="indexterm" name="idm139899452837808"></a><a class="indexterm" name="idm139899452836304"></a><a class="indexterm" name="idm139899452834800"></a><a class="indexterm" name="idm139899452833296"></a><a class="indexterm" name="idm139899452831792"></a><a class="indexterm" name="idm139899452830288"></a><a class="indexterm" name="idm139899452828784"></a><a class="indexterm" name="idm139899452827280"></a><a class="indexterm" name="idm139899452825776"></a><a class="indexterm" name="idm139899452824272"></a><a class="indexterm" name="idm139899452822768"></a><a class="indexterm" name="idm139899452821696"></a><a class="indexterm" name="idm139899452820176"></a><a class="indexterm" name="idm139899452818656"></a><a class="indexterm" name="idm139899452817136"></a><a class="indexterm" name="idm139899452815632"></a><a class="indexterm" name="idm139899452814144"></a><a class="indexterm" name="idm139899452812656"></a><p>
      This set of Frequently Asked Questions derives from the experience
      of MySQL's Support and Development groups in handling many
      inquiries about CJK (Chinese-Japanese-Korean) issues.
</p>
<div class="qandaset">
<a name="idm139899452810560"></a><dl><dt>A.11.1. <a href="faqs.html#faq-cjk-what-cjk-avail">
        What CJK character sets are available in MySQL?
      </a></dt><dt>A.11.2. <a href="faqs.html#faq-cjk-why-cjk-question-marks">
        I have inserted CJK characters into my table. Why does
        SELECT display them as
        “?” characters?
      </a></dt><dt>A.11.3. <a href="faqs.html#faq-cjk-what-problems-big5">
        What problems should I be aware of when working with the Big5
        Chinese character set?
      </a></dt><dt>A.11.4. <a href="faqs.html#faq-cjk-what-japanese-chars-fail">
        Why do Japanese character set conversions fail?
      </a></dt><dt>A.11.5. <a href="faqs.html#faq-cjk-how-convert-81ca-cp932">
        What should I do if I want to convert SJIS
        81CA to cp932?
      </a></dt><dt>A.11.6. <a href="faqs.html#faq-cjk-how-represent-yen">
        How does MySQL represent the Yen (¥) sign?
      </a></dt><dt>A.11.7. <a href="faqs.html#faq-cjk-what-problems-korean">
        Of what issues should I be aware when working with Korean
        character sets in MySQL?
      </a></dt><dt>A.11.8. <a href="faqs.html#faq-cjk-why-incorrect-string-value">
        Why do I get Incorrect string value error
        messages?
      </a></dt><dt>A.11.9. <a href="faqs.html#faq-cjk-why-gui-not-display-cjk">
        Why does my GUI front end or browser display CJK characters
        incorrectly in my application using Access, PHP, or another API?
      </a></dt><dt>A.11.10. <a href="faqs.html#faq-cjk-how-use-4-0-charset">
        I've upgraded to MySQL 8.0. How can I revert to
        behavior like that in MySQL 4.0 with regard to character sets?
      </a></dt><dt>A.11.11. <a href="faqs.html#faq-cjk-why-cjk-fail-searches">
        Why do some LIKE and
        FULLTEXT searches with CJK characters fail?
      </a></dt><dt>A.11.12. <a href="faqs.html#faq-cjk-how-know-avail-characters">
        How do I know whether character X is
        available in all character sets?
      </a></dt><dt>A.11.13. <a href="faqs.html#faq-cjk-how-cjk-sort-unicode-1">
        Why do CJK strings sort incorrectly in Unicode? (I)
      </a></dt><dt>A.11.14. <a href="faqs.html#faq-cjk-how-cjk-sort-unicode-2">
        Why do CJK strings sort incorrectly in Unicode? (II)
      </a></dt><dt>A.11.15. <a href="faqs.html#faq-cjk-why-supplement-char-rejected">
        Why are my supplementary characters rejected by MySQL?
      </a></dt><dt>A.11.16. <a href="faqs.html#faq-cjk-why-cjk-not-cjkv">
        Should “CJK” be “CJKV”?
      </a></dt><dt>A.11.17. <a href="faqs.html#faq-cjk-how-cjk-db-table-names">
        Does MySQL permit CJK characters to be used in database and
        table names?
      </a></dt><dt>A.11.18. <a href="faqs.html#faq-cjk-where-doc-translations">
        Where can I find translations of the MySQL Manual into Chinese,
        Japanese, and Korean?
      </a></dt><dt>A.11.19. <a href="faqs.html#faq-cjk-help-with-cjk">
        Where can I get help with CJK and related issues in MySQL?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-cjk-what-cjk-avail"></a><a name="idm139899452809904"></a><p><b>A.11.1.</b></p></td><td align="left" valign="top"><p>
        What CJK character sets are available in MySQL?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The list of CJK character sets may vary depending on your MySQL
        version. For example, the <code class="literal">gb18030</code> character
        set is not supported prior to MySQL 5.7.4. However, since the
        name of the applicable language appears in the
        <code class="literal">DESCRIPTION</code> column for every entry in the
        <a class="link" href="information-schema.html#character-sets-table" title="24.1 The INFORMATION_SCHEMA CHARACTER_SETS Table"><code class="literal">INFORMATION_SCHEMA.CHARACTER_SETS</code></a>
        table, you can obtain a current list of all the non-Unicode CJK
        character sets using this query:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT CHARACTER_SET_NAME, DESCRIPTION</code></strong>
       <strong class="userinput"><code>FROM INFORMATION_SCHEMA.CHARACTER_SETS</code></strong>
       <strong class="userinput"><code>WHERE DESCRIPTION LIKE '%Chin%'</code></strong>
       <strong class="userinput"><code>OR DESCRIPTION LIKE '%Japanese%'</code></strong>
       <strong class="userinput"><code>OR DESCRIPTION LIKE '%Korean%'</code></strong>
       <strong class="userinput"><code>ORDER BY CHARACTER_SET_NAME;</code></strong>
+--------------------+---------------------------------+
| CHARACTER_SET_NAME | DESCRIPTION                     |
+--------------------+---------------------------------+
| big5               | Big5 Traditional Chinese        |
| cp932              | SJIS for Windows Japanese       |
| eucjpms            | UJIS for Windows Japanese       |
| euckr              | EUC-KR Korean                   |
| gb18030            | China National Standard GB18030 |
| gb2312             | GB2312 Simplified Chinese       |
| gbk                | GBK Simplified Chinese          |
| sjis               | Shift-JIS Japanese              |
| ujis               | EUC-JP Japanese                 |
+--------------------+---------------------------------+
</pre><p>
        (For more information, see
        <a class="xref" href="information-schema.html#character-sets-table" title="24.1 The INFORMATION_SCHEMA CHARACTER_SETS Table">Section 24.1, “The INFORMATION_SCHEMA CHARACTER_SETS Table”</a>.)
      </p><p>
        MySQL supports three variants of the
        <span class="firstterm">GB</span> (<span class="foreignphrase"><em class="foreignphrase">Guojia
        Biaozhun</em></span>, or <span class="emphasis"><em>National
        Standard</em></span>, or <span class="emphasis"><em>Simplified Chinese</em></span>)
        character sets which are official in the People's Republic of
        China: <code class="literal">gb2312</code>, <code class="literal">gbk</code>, and
        (as of MySQL 5.7.4) <code class="literal">gb18030</code>.
      </p><p>
        Sometimes people try to insert <code class="literal">gbk</code> characters
        into <code class="literal">gb2312</code>, and it works most of the time
        because <code class="literal">gbk</code> is a superset of
        <code class="literal">gb2312</code>. But eventually they try to insert a
        rarer Chinese character and it does not work. (For an example,
        see Bug #16072).
      </p><p>
        Here, we try to clarify exactly what characters are legitimate
        in <code class="literal">gb2312</code> or <code class="literal">gbk</code>, with
        reference to the official documents. Please check these
        references before reporting <code class="literal">gb2312</code> or
        <code class="literal">gbk</code> bugs:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            The MySQL <code class="literal">gbk</code> character set is in reality
            <span class="quote">“<span class="quote">Microsoft code page 936</span>”</span>. This differs from
            the official <code class="literal">gbk</code> for characters
            <code class="literal">A1A4</code> (middle dot),
            <code class="literal">A1AA</code> (em dash),
            <code class="literal">A6E0-A6F5</code>, and
            <code class="literal">A8BB-A8C0</code>.
          </p></li><li class="listitem"><p>
            For a listing of <code class="literal">gbk</code>/Unicode mappings,
            see
            <a class="ulink" href="http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOWS/CP936.TXT" target="_top">http://www.unicode.org/Public/MAPPINGS/VENDORS/MICSFT/WINDOWS/CP936.TXT</a>.
</p></li></ul>
</div>
<p>
        It is also possible to store CJK characters in Unicode character
        sets, although the available collations may not sort characters
        quite as you expect:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            The <code class="literal">utf8</code> and <code class="literal">ucs2</code>
            character sets support the characters from Unicode Basic
            Multilingual Plane (BMP). These characters have code point
            values between <code class="literal">U+0000</code> and
            <code class="literal">U+FFFF</code>.
          </p></li><li class="listitem"><p>
            The <code class="literal">utf8mb4</code>, <code class="literal">utf16</code>,
            <code class="literal">utf16le</code>, and <code class="literal">utf32</code>
            character sets support BMP characters, as well as
            supplementary characters that lie outside the BMP.
            Supplementary characters have code point values between
            <code class="literal">U+10000</code> and <code class="literal">U+10FFFF</code>.
</p></li></ul>
</div>
<p>
        The collation used for a Unicode character set determines the
        ability to sort (that is, distinguish) characters in the set:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            Collations based on Unicode Collation Algorithm (UCA) 4.0.0
            distinguish only BMP characters.
          </p></li><li class="listitem"><p>
            Collations based on UCA 5.2.0 or 9.0.0 distinguish BMP and
            supplementary characters.
          </p></li><li class="listitem"><p>
            Non-UCA collations may not distinguish all Unicode
            characters. For example, the <code class="literal">utf8mb4</code>
            default collation is <code class="literal">utf8mb4_general_ci</code>,
            which distinguishes only BMP characters.
</p></li></ul>
</div>
<p>
        Moreover, distinguishing characters is not the same as ordering
        them per the conventions of a given CJK language. Currently,
        MySQL has only one CJK-specific UCA collation,
        <code class="literal">gb18030_unicode_520_ci</code> (which requires use of
        the non-Unicode <code class="literal">gb18030</code> character set).
      </p><p>
        For information about Unicode collations and their
        differentiating properties, including collation properties for
        supplementary characters, see
        <a class="xref" href="charset.html#charset-unicode-sets" title="10.10.1 Unicode Character Sets">Section 10.10.1, “Unicode Character Sets”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-why-cjk-question-marks"></a><a name="idm139899452759552"></a><p><b>A.11.2.</b></p></td><td align="left" valign="top"><p>
        I have inserted CJK characters into my table. Why does
        <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> display them as
        <span class="quote">“<span class="quote">?</span>”</span> characters?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        This problem is usually due to a setting in MySQL that does not
        match the settings for the application program or the operating
        system. Here are some common steps for correcting these types of
        issues:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <span class="emphasis"><em>Be certain of what MySQL version you are
            using</em></span>.
          </p><p>
            Use the statement <code class="literal">SELECT VERSION();</code> to
            determine this.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Make sure that the database is actually using the
            desired character set</em></span>.
          </p><p>
            People often think that the client character set is always
            the same as either the server character set or the character
            set used for display purposes. However, both of these are
            false assumptions. You can make sure by checking the result
            of <code class="literal">SHOW CREATE TABLE
            <em class="replaceable"><code>tablename</code></em></code> or, better
            yet, by using this statement:
          </p><pre data-lang="sql" class="programlisting">
SELECT character_set_name, collation_name
    FROM information_schema.columns
    WHERE table_schema = your_database_name
        AND table_name = your_table_name
        AND column_name = your_column_name;
</pre></li><li class="listitem"><p>
            <span class="emphasis"><em>Determine the hexadecimal value of the character
            or characters that are not being displayed
            correctly</em></span>.
          </p><p>
            You can obtain this information for a column
            <em class="replaceable"><code>column_name</code></em> in the table
            <em class="replaceable"><code>table_name</code></em> using the following
            query:
          </p><pre data-lang="sql" class="programlisting">
SELECT HEX(<em class="replaceable"><code>column_name</code></em>)
FROM <em class="replaceable"><code>table_name</code></em>;
</pre><p>
            <code class="literal">3F</code> is the encoding for the
            <code class="literal">?</code> character; this means that
            <code class="literal">?</code> is the character actually stored in the
            column. This most often happens because of a problem
            converting a particular character from your client character
            set to the target character set.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Make sure that a round trip is possible. When you
            select <em class="replaceable"><code>literal</code></em> (or
            <em class="replaceable"><code>_introducer hexadecimal-value</code></em>),
            do you obtain <em class="replaceable"><code>literal</code></em> as a
            result</em></span>?
          </p><p>
            For example, the Japanese Katakana character
            <span class="foreignphrase"><em class="foreignphrase">Pe</em></span> (<code class="literal">ペ'</code>)
            exists in all CJK character sets, and has the code point
            value (hexadecimal coding) <code class="literal">0x30da</code>. To
            test a round trip for this character, use this query:
          </p><pre data-lang="sql" class="programlisting">
SELECT 'ペ' AS `ペ`;         /* or SELECT _ucs2 0x30da; */
</pre><p>
            If the result is not also <code class="literal">ペ</code>, the round
            trip failed.
          </p><p>
            For bug reports regarding such failures, we might ask you to
            follow up with <code class="literal">SELECT HEX('ペ');</code>. Then
            we can determine whether the client encoding is correct.
          </p></li><li class="listitem"><p>
            <span class="emphasis"><em>Make sure that the problem is not with the browser
            or other application, rather than with MySQL</em></span>.
          </p><p>
            Use the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> client program to
            accomplish this task. If <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> displays
            characters correctly but your application does not, your
            problem is probably due to system settings.
          </p><p>
            To determine your settings, use the
            <a class="link" href="sql-syntax.html#show-variables" title="13.7.6.39 SHOW VARIABLES Syntax"><code class="literal">SHOW VARIABLES</code></a> statement,
            whose output should resemble what is shown here:
          </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW VARIABLES LIKE 'char%';</code></strong>
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8                                   |
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
</pre><p>
            These are typical character-set settings for an
            international-oriented client (notice the use of
            <code class="literal">utf8</code> Unicode) connected to a server in
            the West (<code class="literal">latin1</code> is a West Europe
            character set).
          </p><p>
            Although Unicode (usually the <code class="literal">utf8</code>
            variant on Unix, and the <code class="literal">ucs2</code> variant on
            Windows) is preferable to Latin, it is often not what your
            operating system utilities support best. Many Windows users
            find that a Microsoft character set, such as
            <code class="literal">cp932</code> for Japanese Windows, is suitable.
          </p><p>
            If you cannot control the server settings, and you have no
            idea what setting your underlying computer uses, try
            changing to a common character set for the country that
            you're in (<code class="literal">euckr</code> = Korea;
            <code class="literal">gb18030</code>, <code class="literal">gb2312</code> or
            <code class="literal">gbk</code> = People's Republic of China;
            <code class="literal">big5</code> = Taiwan; <code class="literal">sjis</code>,
            <code class="literal">ujis</code>, <code class="literal">cp932</code>, or
            <code class="literal">eucjpms</code> = Japan; <code class="literal">ucs2</code>
            or <code class="literal">utf8</code> = anywhere). Usually it is
            necessary to change only the client and connection and
            results settings. The <a class="link" href="sql-syntax.html#set-names" title="13.7.5.3 SET NAMES Syntax"><code class="literal">SET
            NAMES</code></a>. statement changes all three at once. For
            example:
          </p><pre data-lang="sql" class="programlisting">
SET NAMES 'big5';
</pre><p>
            Once the setting is correct, you can make it permanent by
            editing <code class="filename">my.cnf</code> or
            <code class="filename">my.ini</code>. For example you might add lines
            looking like these:
          </p><pre data-lang="ini" class="programlisting">
[mysqld]
character-set-server=big5
[client]
default-character-set=big5
</pre><p>
            It is also possible that there are issues with the API
            configuration setting being used in your application; see
            <em class="citetitle">Why does my GUI front end or browser not display
            CJK characters correctly...?</em> for more
            information.
</p></li></ul>
</div>
</td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-what-problems-big5"></a><a name="idm139899452704288"></a><p><b>A.11.3.</b></p></td><td align="left" valign="top"><p>
        What problems should I be aware of when working with the Big5
        Chinese character set?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        MySQL supports the Big5 character set which is common in Hong
        Kong and Taiwan (Republic of China). The MySQL
        <code class="literal">big5</code> character set is in reality Microsoft
        code page 950, which is very similar to the original
        <code class="literal">big5</code> character set.
      </p><p>
        A feature request for adding <code class="literal">HKSCS</code> extensions
        has been filed. People who need this extension may find the
        suggested patch for Bug #13577 to be of interest.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-what-japanese-chars-fail"></a><a name="idm139899452699344"></a><p><b>A.11.4.</b></p></td><td align="left" valign="top"><p>
        Why do Japanese character set conversions fail?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        MySQL supports the <code class="literal">sjis</code>,
        <code class="literal">ujis</code>, <code class="literal">cp932</code>, and
        <code class="literal">eucjpms</code> character sets, as well as Unicode. A
        common need is to convert between character sets. For example,
        there might be a Unix server (typically with
        <code class="literal">sjis</code> or <code class="literal">ujis</code>) and a
        Windows client (typically with <code class="literal">cp932</code>).
      </p><p>
        In the following conversion table, the <code class="literal">ucs2</code>
        column represents the source, and the <code class="literal">sjis</code>,
        <code class="literal">cp932</code>, <code class="literal">ujis</code>, and
        <code class="literal">eucjpms</code> columns represent the destinations;
        that is, the last 4 columns provide the hexadecimal result when
        we use <a class="link" href="functions.html#function_convert"><code class="literal">CONVERT(ucs2)</code></a> or we assign
        a <code class="literal">ucs2</code> column containing the value to an
        <code class="literal">sjis</code>, <code class="literal">cp932</code>,
        <code class="literal">ujis</code>, or <code class="literal">eucjpms</code> column.
</p>
<div class="informaltable">
<table summary="Result of assigning various ucs2 characters to a column that uses the sjis, cp932, ujis, or eucjpms character set."><col width="40%"><col align="left" width="12%"><col align="right" width="12%"><col align="right" width="12%"><col align="right" width="12%"><col align="right" width="12%"><thead><tr>
            <th scope="col">Character Name</th>
            <th scope="col">ucs2</th>
            <th scope="col">sjis</th>
            <th scope="col">cp932</th>
            <th scope="col">ujis</th>
            <th scope="col">eucjpms</th>
          </tr></thead><tbody><tr>
            <td scope="row">BROKEN BAR</td>
            <td>00A6</td>
            <td>3F</td>
            <td>3F</td>
            <td>8FA2C3</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">FULLWIDTH BROKEN BAR</td>
            <td>FFE4</td>
            <td>3F</td>
            <td>FA55</td>
            <td>3F</td>
            <td>8FA2</td>
          </tr><tr>
            <td scope="row">YEN SIGN</td>
            <td>00A5</td>
            <td>3F</td>
            <td>3F</td>
            <td>20</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">FULLWIDTH YEN SIGN</td>
            <td>FFE5</td>
            <td>818F</td>
            <td>818F</td>
            <td>A1EF</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">TILDE</td>
            <td>007E</td>
            <td>7E</td>
            <td>7E</td>
            <td>7E</td>
            <td>7E</td>
          </tr><tr>
            <td scope="row">OVERLINE</td>
            <td>203E</td>
            <td>3F</td>
            <td>3F</td>
            <td>20</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">HORIZONTAL BAR</td>
            <td>2015</td>
            <td>815C</td>
            <td>815C</td>
            <td>A1BD</td>
            <td>A1BD</td>
          </tr><tr>
            <td scope="row">EM DASH</td>
            <td>2014</td>
            <td>3F</td>
            <td>3F</td>
            <td>3F</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">REVERSE SOLIDUS</td>
            <td>005C</td>
            <td>815F</td>
            <td>5C</td>
            <td>5C</td>
            <td>5C</td>
          </tr><tr>
            <td scope="row">FULLWIDTH ""</td>
            <td>FF3C</td>
            <td>3F</td>
            <td>815F</td>
            <td>3F</td>
            <td>A1C0</td>
          </tr><tr>
            <td scope="row">WAVE DASH</td>
            <td>301C</td>
            <td>8160</td>
            <td>3F</td>
            <td>A1C1</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">FULLWIDTH TILDE</td>
            <td>FF5E</td>
            <td>3F</td>
            <td>8160</td>
            <td>3F</td>
            <td>A1C1</td>
          </tr><tr>
            <td scope="row">DOUBLE VERTICAL LINE</td>
            <td>2016</td>
            <td>8161</td>
            <td>3F</td>
            <td>A1C2</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">PARALLEL TO</td>
            <td>2225</td>
            <td>3F</td>
            <td>8161</td>
            <td>3F</td>
            <td>A1C2</td>
          </tr><tr>
            <td scope="row">MINUS SIGN</td>
            <td>2212</td>
            <td>817C</td>
            <td>3F</td>
            <td>A1DD</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">FULLWIDTH HYPHEN-MINUS</td>
            <td>FF0D</td>
            <td>3F</td>
            <td>817C</td>
            <td>3F</td>
            <td>A1DD</td>
          </tr><tr>
            <td scope="row">CENT SIGN</td>
            <td>00A2</td>
            <td>8191</td>
            <td>3F</td>
            <td>A1F1</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">FULLWIDTH CENT SIGN</td>
            <td>FFE0</td>
            <td>3F</td>
            <td>8191</td>
            <td>3F</td>
            <td>A1F1</td>
          </tr><tr>
            <td scope="row">POUND SIGN</td>
            <td>00A3</td>
            <td>8192</td>
            <td>3F</td>
            <td>A1F2</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">FULLWIDTH POUND SIGN</td>
            <td>FFE1</td>
            <td>3F</td>
            <td>8192</td>
            <td>3F</td>
            <td>A1F2</td>
          </tr><tr>
            <td scope="row">NOT SIGN</td>
            <td>00AC</td>
            <td>81CA</td>
            <td>3F</td>
            <td>A2CC</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">FULLWIDTH NOT SIGN</td>
            <td>FFE2</td>
            <td>3F</td>
            <td>81CA</td>
            <td>3F</td>
            <td>A2CC</td>
</tr></tbody></table>
</div>
<p>
        Now consider the following portion of the table.
</p>
<div class="informaltable">
<table summary="Result of assigning NOT SIGN and FULLWIDTH NOT SIGN ucs2 characters to a column that uses the sjis, cp932, ujis, or eucjpms character set."><col width="55%"><col align="right" width="15%"><col align="right" width="15%"><col align="right" width="15%"><thead><tr>
            <th scope="col"></th>
            <th scope="col">ucs2</th>
            <th scope="col">sjis</th>
            <th scope="col">cp932</th>
          </tr></thead><tbody><tr>
            <td scope="row">NOT SIGN</td>
            <td>00AC</td>
            <td>81CA</td>
            <td>3F</td>
          </tr><tr>
            <td scope="row">FULLWIDTH NOT SIGN</td>
            <td>FFE2</td>
            <td>3F</td>
            <td>81CA</td>
</tr></tbody></table>
</div>
<p>
        This means that MySQL converts the <code class="literal">NOT SIGN</code>
        (Unicode <code class="literal">U+00AC</code>) to <code class="literal">sjis</code>
        code point <code class="literal">0x81CA</code> and to
        <code class="literal">cp932</code> code point <code class="literal">3F</code>.
        (<code class="literal">3F</code> is the question mark (<span class="quote">“<span class="quote">?</span>”</span>.
        This is what is always used when the conversion cannot be
        performed.)
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-how-convert-81ca-cp932"></a><a name="idm139899452513408"></a><p><b>A.11.5.</b></p></td><td align="left" valign="top"><p>
        What should I do if I want to convert SJIS
        <code class="literal">81CA</code> to <code class="literal">cp932</code>?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Our answer is: <span class="quote">“<span class="quote">?</span>”</span>. There are disadvantages to
        this, and many people would prefer a <span class="quote">“<span class="quote">loose</span>”</span>
        conversion, so that <code class="literal">81CA (NOT SIGN)</code> in
        <code class="literal">sjis</code> becomes <code class="literal">81CA (FULLWIDTH NOT
        SIGN)</code> in <code class="literal">cp932</code>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-how-represent-yen"></a><a name="idm139899452506144"></a><p><b>A.11.6.</b></p></td><td align="left" valign="top"><p>
        How does MySQL represent the Yen (<code class="literal">¥</code>) sign?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        A problem arises because some versions of Japanese character
        sets (both <code class="literal">sjis</code> and <code class="literal">euc</code>)
        treat <code class="literal">5C</code> as a
        <span class="firstterm">reverse solidus</span>
        (<code class="literal">\</code>, also known as a backslash), whereas
        others treat it as a yen sign (<code class="literal">¥</code>).
      </p><p>
        MySQL follows only one version of the JIS (Japanese Industrial
        Standards) standard description. In MySQL,
        <span class="emphasis"><em><code class="literal">5C</code> is always the reverse solidus
        (<code class="literal">\</code>)</em></span>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-what-problems-korean"></a><a name="idm139899452496864"></a><p><b>A.11.7.</b></p></td><td align="left" valign="top"><p>
        Of what issues should I be aware when working with Korean
        character sets in MySQL?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        In theory, while there have been several versions of the
        <code class="literal">euckr</code> (<span class="firstterm">Extended
        Unix Code Korea</span>) character set, only one problem has
        been noted. We use the <span class="quote">“<span class="quote">ASCII</span>”</span> variant of EUC-KR,
        in which the code point <code class="literal">0x5c</code> is REVERSE
        SOLIDUS, that is <code class="literal">\</code>, instead of the
        <span class="quote">“<span class="quote">KS-Roman</span>”</span> variant of EUC-KR, in which the code
        point <code class="literal">0x5c</code> is <code class="literal">WON SIGN</code>
        (<code class="literal">₩</code>). This means that you cannot convert
        Unicode <code class="literal">U+20A9</code> to <code class="literal">euckr</code>:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT</code></strong>
           <strong class="userinput"><code>CONVERT('₩' USING euckr) AS euckr,</code></strong>
           <strong class="userinput"><code>HEX(CONVERT('₩' USING euckr)) AS hexeuckr;</code></strong>
+-------+----------+
| euckr | hexeuckr |
+-------+----------+
| ?     | 3F       |
+-------+----------+
</pre></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-why-incorrect-string-value"></a><a name="idm139899452484016"></a><p><b>A.11.8.</b></p></td><td align="left" valign="top"><p>
        Why do I get <span class="errortext">Incorrect string value</span> error
        messages?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        To see the problem, create a table with one Unicode
        (<code class="literal">ucs2</code>) column and one Chinese
        (<code class="literal">gb2312</code>) column.
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE ch</code></strong>
       <strong class="userinput"><code>(ucs2 CHAR(3) CHARACTER SET ucs2,</code></strong>
       <strong class="userinput"><code>gb2312 CHAR(3) CHARACTER SET gb2312);</code></strong>
</pre><p>
        In nonstrict SQL mode, try to place the rare character
        <code class="literal">汌</code> in both columns.
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET sql_mode = '';</code></strong>
mysql&gt; <strong class="userinput"><code>INSERT INTO ch VALUES ('A汌B','A汌B');</code></strong>
Query OK, 1 row affected, 1 warning (0.00 sec)
</pre><p>
        The <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a> produces a warning.
        Use the following statement to see what it is:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW WARNINGS\G</code></strong>
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect string value: '\xE6\xB1\x8CB' for column 'gb2312' at row 1
</pre><p>
        So it is a warning about the <code class="literal">gb2312</code> column
        only.
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; SELECT ucs2,HEX(ucs2),gb2312,HEX(gb2312) FROM ch;
+-------+--------------+--------+-------------+
| ucs2  | HEX(ucs2)    | gb2312 | HEX(gb2312) |
+-------+--------------+--------+-------------+
| A汌B | 00416C4C0042 | A?B    | 413F42      |
+-------+--------------+--------+-------------+
</pre><p>
        Several things need explanation here:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
            The <code class="literal">汌</code> character is not in the
            <code class="literal">gb2312</code> character set, as described
            earlier.
          </p></li><li class="listitem"><p>
            If you are using an old version of MySQL, you may see a
            different message.
          </p></li><li class="listitem"><p>
            A warning occurs rather than an error because MySQL is not
            set to use strict SQL mode. In nonstrict mode, MySQL tries
            to do what it can, to get the best fit, rather than give up.
            With strict SQL mode, the <span class="errortext">Incorrect string
            value</span> message occurs as an error rather than a
            warning, and the <a class="link" href="sql-syntax.html#insert" title="13.2.6 INSERT Syntax"><code class="literal">INSERT</code></a>
            fails.
</p></li></ol>
</div>
</td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-why-gui-not-display-cjk"></a><a name="idm139899452460016"></a><p><b>A.11.9.</b></p></td><td align="left" valign="top"><p>
        Why does my GUI front end or browser display CJK characters
        incorrectly in my application using Access, PHP, or another API?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Obtain a direct connection to the server using the
        <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> client, and try the same query there.
        If <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> responds correctly, the trouble may
        be that your application interface requires initialization. Use
        <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> to tell you what character set or sets
        it uses with the statement <code class="literal">SHOW VARIABLES LIKE
        'char%';</code>. If you are using Access, you are most likely
        connecting with Connector/ODBC. In this case, you should check
        <a class="ulink" href="http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration.html" target="_top">Configuring Connector/ODBC</a>. If, for example,
        you use <code class="literal">big5</code>, you would enter <code class="literal">SET
        NAMES 'big5'</code>. (In this case, no <code class="literal">;</code>
        character is required.) If you are using ASP, you might need to
        add <a class="link" href="sql-syntax.html#set-names" title="13.7.5.3 SET NAMES Syntax"><code class="literal">SET NAMES</code></a> in the code. Here
        is an example that has worked in the past:
      </p><pre data-lang="none" class="programlisting">
&lt;%
Session.CodePage=0
Dim strConnection
Dim Conn
strConnection="driver={MySQL ODBC 3.51 Driver};server=<em class="replaceable"><code>server</code></em>;uid=<em class="replaceable"><code>username</code></em>;" \
               &amp; "pwd=<em class="replaceable"><code>password</code></em>;database=<em class="replaceable"><code>database</code></em>;stmt=SET NAMES 'big5';"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConnection
%&gt;
</pre><p>
        In much the same way, if you are using any character set other
        than <code class="literal">latin1</code> with Connector/Net, you must
        specify the character set in the connection string. See
        <a class="ulink" href="http://dev.mysql.com/doc/connector-net/en/connector-net-programming-connecting.html" target="_top">Connecting to MySQL Using Connector/Net</a>, for more
        information.
      </p><p>
        If you are using PHP, try this:
      </p><pre data-lang="php" class="programlisting">
&lt;?php
  $link = new mysqli($host, $usr, $pwd, $db);

  if( mysqli_connect_errno() )
  {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
  }

  $link-&gt;query("SET NAMES 'utf8'");
?&gt;
</pre><p>
        In this case, we used <a class="link" href="sql-syntax.html#set-names" title="13.7.5.3 SET NAMES Syntax"><code class="literal">SET NAMES</code></a>
        to change <a class="link" href="server-administration.html#sysvar_character_set_client"><code class="literal">character_set_client</code></a>,
        <a class="link" href="server-administration.html#sysvar_character_set_connection"><code class="literal">character_set_connection</code></a>, and
        <a class="link" href="server-administration.html#sysvar_character_set_results"><code class="literal">character_set_results</code></a>.
      </p><p>
        Another issue often encountered in PHP applications has to do
        with assumptions made by the browser. Sometimes adding or
        changing a <code class="literal">&lt;meta&gt;</code> tag suffices to
        correct the problem: for example, to insure that the user agent
        interprets page content as <code class="literal">UTF-8</code>, include
        <code class="literal">&lt;meta http-equiv="Content-Type" content="text/html;
        charset=utf-8"&gt;</code> in the
        <code class="literal">&lt;head&gt;</code> section of the HTML page.
      </p><p>
        If you are using Connector/J, see
        <a class="ulink" href="http://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-charsets.html" target="_top">Using Character Sets and Unicode</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-how-use-4-0-charset"></a><a name="idm139899452431408"></a><p><b>A.11.10.</b></p></td><td align="left" valign="top"><p>
        I've upgraded to MySQL 8.0. How can I revert to
        behavior like that in MySQL 4.0 with regard to character sets?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        In MySQL Version 4.0, there was a single <span class="quote">“<span class="quote">global</span>”</span>
        character set for both server and client, and the decision as to
        which character to use was made by the server administrator.
        This changed starting with MySQL Version 4.1. What happens now
        is a <span class="quote">“<span class="quote">handshake</span>”</span>, as described in
        <a class="xref" href="charset.html#charset-connection" title="10.4 Connection Character Sets and Collations">Section 10.4, “Connection Character Sets and Collations”</a>:
</p>
<div class="blockquote">
<blockquote class="blockquote"><p>
          When a client connects, it sends to the server the name of the
          character set that it wants to use. The server uses the name
          to set the
          <a class="link" href="server-administration.html#sysvar_character_set_client"><code class="literal">character_set_client</code></a>,
          <a class="link" href="server-administration.html#sysvar_character_set_results"><code class="literal">character_set_results</code></a>, and
          <a class="link" href="server-administration.html#sysvar_character_set_connection"><code class="literal">character_set_connection</code></a>
          system variables. In effect, the server performs a
          <a class="link" href="sql-syntax.html#set-names" title="13.7.5.3 SET NAMES Syntax"><code class="literal">SET NAMES</code></a> operation using the
          character set name.
</p></blockquote>
</div>
<p>
        The effect of this is that you cannot control the client
        character set by starting <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> with
        <a class="link" href="server-administration.html#option_mysqld_character-set-server"><code class="option">--character-set-server=utf8</code></a>.
        However, some Asian customers prefer the MySQL 4.0 behavior. To
        make it possible to retain this behavior, we added a
        <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> switch,
        <a class="link" href="server-administration.html#option_mysqld_character-set-client-handshake"><code class="option">--character-set-client-handshake</code></a>,
        which can be turned off with
        <a class="link" href="server-administration.html#option_mysqld_character-set-client-handshake"><code class="option">--skip-character-set-client-handshake</code></a>.
        If you start <a class="link" href="programs.html#mysqld" title="4.3.1 mysqld — The MySQL Server"><span class="command"><strong>mysqld</strong></span></a> with
        <a class="link" href="server-administration.html#option_mysqld_character-set-client-handshake"><code class="option">--skip-character-set-client-handshake</code></a>,
        then, when a client connects, it sends to the server the name of
        the character set that it wants to use. However, <span class="emphasis"><em>the
        server ignores this request from the client</em></span>.
      </p><p>
        By way of example, suppose that your favorite server character
        set is <code class="literal">latin1</code> (unlikely in a CJK area, but
        this is the default value). Suppose further that the client uses
        <code class="literal">utf8</code> because this is what the client's
        operating system supports. Now, start the server with
        <code class="literal">latin1</code> as its default character set:
      </p><pre data-lang="terminal" class="programlisting">
mysqld --character-set-server=latin1
</pre><p>
        And then start the client with the default character set
        <code class="literal">utf8</code>:
      </p><pre data-lang="terminal" class="programlisting">
mysql --default-character-set=utf8
</pre><p>
        The resulting settings can be seen by viewing the output of
        <a class="link" href="sql-syntax.html#show-variables" title="13.7.6.39 SHOW VARIABLES Syntax"><code class="literal">SHOW VARIABLES</code></a>:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW VARIABLES LIKE 'char%';</code></strong>
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | utf8                                   |
| character_set_connection | utf8                                   |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | utf8                                   |
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
</pre><p>
        Now stop the client, and stop the server using
        <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin</strong></span></a>. Then start the server again, but
        this time tell it to skip the handshake like so:
      </p><pre data-lang="terminal" class="programlisting">
mysqld --character-set-server=utf8 --skip-character-set-client-handshake
</pre><p>
        Start the client with <code class="literal">utf8</code> once again as the
        default character set, then display the resulting settings:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW VARIABLES LIKE 'char%';</code></strong>
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_client     | latin1                                 |
| character_set_connection | latin1                                 |
| character_set_database   | latin1                                 |
| character_set_filesystem | binary                                 |
| character_set_results    | latin1                                 |
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_sets_dir       | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
</pre><p>
        As you can see by comparing the differing results from
        <a class="link" href="sql-syntax.html#show-variables" title="13.7.6.39 SHOW VARIABLES Syntax"><code class="literal">SHOW VARIABLES</code></a>, the server
        ignores the client's initial settings if the
        <a class="link" href="server-administration.html#option_mysqld_character-set-client-handshake"><code class="option">--skip-character-set-client-handshake</code></a>
        option is used.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-why-cjk-fail-searches"></a><a name="idm139899452391968"></a><p><b>A.11.11.</b></p></td><td align="left" valign="top"><p>
        Why do some <a class="link" href="functions.html#operator_like"><code class="literal">LIKE</code></a> and
        <code class="literal">FULLTEXT</code> searches with CJK characters fail?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        For <a class="link" href="functions.html#operator_like"><code class="literal">LIKE</code></a> searches, there is a very
        simple problem with binary string column types such as
        <a class="link" href="data-types.html#binary-varbinary" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">BINARY</code></a> and
        <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a>: we must know where
        characters end. With multibyte character sets, different
        characters might have different octet lengths. For example, in
        <code class="literal">utf8</code>, <code class="literal">A</code> requires one byte
        but <code class="literal">ペ</code> requires three bytes, as shown here:
      </p><pre data-lang="none" class="programlisting">
+-------------------------+---------------------------+
| OCTET_LENGTH(_utf8 'A') | OCTET_LENGTH(_utf8 'ペ') |
+-------------------------+---------------------------+
|                       1 |                         3 |
+-------------------------+---------------------------+
</pre><p>
        If we do not know where the first character in a string ends, we
        do not know where the second character begins, in which case
        even very simple searches such as
        <a class="link" href="functions.html#operator_like"><code class="literal">LIKE '_A%'</code></a> fail.
        The solution is to use a nonbinary string column type defined to
        have the proper CJK character set. For example: <code class="literal">mycol
        TEXT CHARACTER SET sjis</code>. Alternatively, convert to a
        CJK character set before comparing.
      </p><p>
        This is one reason why MySQL cannot permit encodings of
        nonexistent characters. If it is not strict about rejecting bad
        input, it has no way of knowing where characters end.
      </p><p>
        For <code class="literal">FULLTEXT</code> searches, we must know where
        words begin and end. With Western languages, this is rarely a
        problem because most (if not all) of these use an
        easy-to-identify word boundary: the space character. However,
        this is not usually the case with Asian writing. We could use
        arbitrary halfway measures, like assuming that all Han
        characters represent words, or (for Japanese) depending on
        changes from Katakana to Hiragana due to grammatical endings.
        However, the only sure solution requires a comprehensive word
        list, which means that we would have to include a dictionary in
        the server for each Asian language supported. This is simply not
        feasible.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-how-know-avail-characters"></a><a name="idm139899452375280"></a><p><b>A.11.12.</b></p></td><td align="left" valign="top"><p>
        How do I know whether character <em class="replaceable"><code>X</code></em> is
        available in all character sets?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The majority of simplified Chinese and basic nonhalfwidth
        Japanese Kana characters appear in all CJK character sets. The
        following stored procedure accepts a <code class="literal">UCS-2</code>
        Unicode character, converts it to other character sets, and
        displays the results in hexadecimal.
      </p><pre data-lang="sql" class="programlisting">
DELIMITER //

CREATE PROCEDURE p_convert(ucs2_char CHAR(1) CHARACTER SET ucs2)
BEGIN

CREATE TABLE tj
             (ucs2 CHAR(1) character set ucs2,
              utf8 CHAR(1) character set utf8,
              big5 CHAR(1) character set big5,
              cp932 CHAR(1) character set cp932,
              eucjpms CHAR(1) character set eucjpms,
              euckr CHAR(1) character set euckr,
              gb2312 CHAR(1) character set gb2312,
              gbk CHAR(1) character set gbk,
              sjis CHAR(1) character set sjis,
              ujis CHAR(1) character set ujis);

INSERT INTO tj (ucs2) VALUES (ucs2_char);

UPDATE tj SET utf8=ucs2,
              big5=ucs2,
              cp932=ucs2,
              eucjpms=ucs2,
              euckr=ucs2,
              gb2312=ucs2,
              gbk=ucs2,
              sjis=ucs2,
              ujis=ucs2;

/* If there are conversion problems, UPDATE produces warnings. */

SELECT hex(ucs2) AS ucs2,
       hex(utf8) AS utf8,
       hex(big5) AS big5,
       hex(cp932) AS cp932,
       hex(eucjpms) AS eucjpms,
       hex(euckr) AS euckr,
       hex(gb2312) AS gb2312,
       hex(gbk) AS gbk,
       hex(sjis) AS sjis,
       hex(ujis) AS ujis
FROM tj;

DROP TABLE tj;

END//

DELIMITER ;
</pre><p>
        The input can be any single <code class="literal">ucs2</code> character,
        or it can be the code value (hexadecimal representation) of that
        character. For example, from Unicode's list of
        <code class="literal">ucs2</code> encodings and names
        (<a class="ulink" href="http://www.unicode.org/Public/UNIDATA/UnicodeData.txt" target="_top">http://www.unicode.org/Public/UNIDATA/UnicodeData.txt</a>),
        we know that the Katakana character
        <span class="foreignphrase"><em class="foreignphrase">Pe</em></span> appears in all CJK character
        sets, and that its code value is <code class="literal">X'30DA'</code>. If
        we use this value as the argument to
        <code class="literal">p_convert()</code>, the result is as shown here:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CALL p_convert(X'30DA');</code></strong>
+------+--------+------+-------+---------+-------+--------+------+------+------+
| ucs2 | utf8   | big5 | cp932 | eucjpms | euckr | gb2312 | gbk  | sjis | ujis |
+------+--------+------+-------+---------+-------+--------+------+------+------+
| 30DA | E3839A | C772 | 8379  | A5DA    | ABDA  | A5DA   | A5DA | 8379 | A5DA |
+------+--------+------+-------+---------+-------+--------+------+------+------+
</pre><p>
        Since none of the column values is <code class="literal">3F</code> (that
        is, the question mark character, <code class="literal">?</code>), we know
        that every conversion worked.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-how-cjk-sort-unicode-1"></a><a name="idm139899452361184"></a><p><b>A.11.13.</b></p></td><td align="left" valign="top"><p>
        Why do CJK strings sort incorrectly in Unicode? (I)
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        CJK sorting problems that occurred in older MySQL versions can
        be solved as of MySQL 8.0 by using the
        <code class="literal">utf8mb4</code> character set and the
        <code class="literal">utf8mb4_ja_0900_as_cs</code> collation.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-how-cjk-sort-unicode-2"></a><a name="idm139899452357648"></a><p><b>A.11.14.</b></p></td><td align="left" valign="top"><p>
        Why do CJK strings sort incorrectly in Unicode? (II)
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        CJK sorting problems that occurred in older MySQL versions can
        be solved as of MySQL 8.0 by using the
        <code class="literal">utf8mb4</code> character set and the
        <code class="literal">utf8mb4_ja_0900_as_cs</code> collation.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-why-supplement-char-rejected"></a><a name="idm139899452354048"></a><p><b>A.11.15.</b></p></td><td align="left" valign="top"><p>
        Why are my supplementary characters rejected by MySQL?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Supplementary characters lie outside the Unicode <span class="emphasis"><em>Basic
        Multilingual Plane / Plane 0</em></span>. BMP characters have
        code point values between <code class="literal">U+0000</code> and
        <code class="literal">U+FFFF</code>. Supplementary characters have code
        point values between <code class="literal">U+10000</code> and
        <code class="literal">U+10FFFF</code>.
      </p><p>
        To store supplementary characters, you must use a character set
        that permits them:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            The <code class="literal">utf8</code> and <code class="literal">ucs2</code>
            character sets support BMP characters only.
          </p><p>
            The <code class="literal">utf8</code> character set permits only
            <code class="literal">UTF-8</code> characters that take up to three
            bytes. This has led to reports such as that found in Bug
            #12600, which we rejected as <span class="quote">“<span class="quote">not a bug</span>”</span>. With
            <code class="literal">utf8</code>, MySQL must truncate an input string
            when it encounters bytes that it does no understand.
            Otherwise, it is unknown how long the bad multibyte
            character is.
          </p><p>
            One possible workaround is to use <code class="literal">ucs2</code>
            instead of <code class="literal">utf8</code>, in which case the
            <span class="quote">“<span class="quote">bad</span>”</span> characters are changed to question marks.
            However, no truncation takes place. You can also change the
            data type to <a class="link" href="data-types.html#blob" title="11.4.3 The BLOB and TEXT Types"><code class="literal">BLOB</code></a> or
            <a class="link" href="data-types.html#binary-varbinary" title="11.4.2 The BINARY and VARBINARY Types"><code class="literal">BINARY</code></a>, which perform no
            validity checking.
          </p></li><li class="listitem"><p>
            The <code class="literal">utf8mb4</code>, <code class="literal">utf16</code>,
            <code class="literal">utf16le</code>, and <code class="literal">utf32</code>
            character sets support BMP characters, as well as
            supplementary characters outside the BMP.
</p></li></ul>
</div>
</td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-why-cjk-not-cjkv"></a><a name="idm139899452333280"></a><p><b>A.11.16.</b></p></td><td align="left" valign="top"><p>
        Should <span class="quote">“<span class="quote">CJK</span>”</span> be <span class="quote">“<span class="quote">CJKV</span>”</span>?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No. The term <span class="quote">“<span class="quote">CJKV</span>”</span>
        (<span class="firstterm">Chinese Japanese Korean
        Vietnamese</span>) refers to Vietnamese character sets which
        contain Han (originally Chinese) characters. MySQL supports the
        modern Vietnamese script with Western characters, but does not
        support the old Vietnamese script using Han characters.
      </p><p>
        As of MySQL 5.6, there are Vietnamese collations for Unicode
        character sets, as described in
        <a class="xref" href="charset.html#charset-unicode-sets" title="10.10.1 Unicode Character Sets">Section 10.10.1, “Unicode Character Sets”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-how-cjk-db-table-names"></a><a name="idm139899452327824"></a><p><b>A.11.17.</b></p></td><td align="left" valign="top"><p>
        Does MySQL permit CJK characters to be used in database and
        table names?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-where-doc-translations"></a><a name="idm139899452325824"></a><p><b>A.11.18.</b></p></td><td align="left" valign="top"><p>
        Where can I find translations of the MySQL Manual into Chinese,
        Japanese, and Korean?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The Japanese translation of the MySQL 5.6 manual can be
        downloaded from <a class="ulink" href="http://dev.mysql.com/doc/" target="_top">http://dev.mysql.com/doc/</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-cjk-help-with-cjk"></a><a name="idm139899452323200"></a><p><b>A.11.19.</b></p></td><td align="left" valign="top"><p>
        Where can I get help with CJK and related issues in MySQL?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The following resources are available:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
              A listing of MySQL user groups can be found at
              <a class="ulink" href="https://wikis.oracle.com/display/mysql/List+of+MySQL+User+Groups" target="_top">https://wikis.oracle.com/display/mysql/List+of+MySQL+User+Groups</a>.
            </p></li><li class="listitem"><p>
              View feature requests relating to character set issues at
              <a class="ulink" href="http://tinyurl.com/y6xcuf" target="_top">http://tinyurl.com/y6xcuf</a>.
            </p></li><li class="listitem"><p>
              Visit the MySQL
              <a class="ulink" href="http://forums.mysql.com/list.php?103" target="_top">Character Sets,
              Collation, Unicode Forum</a>.
              <a class="ulink" href="http://forums.mysql.com/" target="_top">http://forums.mysql.com/</a> also provides
              foreign-language forums.
</p></li></ul>
</div>
<p>
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-connectors-apis"></a>A.12 MySQL 8.0 FAQ: Connectors &amp; APIs</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139899452314848"></a><p>
      For common questions, issues, and answers relating to the MySQL
      Connectors and other APIs, see the following areas of the Manual:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <a class="xref" href="connectors-apis.html#c-api-common-issues" title="27.7.25 C API Common Issues">Section 27.7.25, “C API Common Issues”</a>
        </p></li><li class="listitem"><p>
          <a class="ulink" href="http://dev.mysql.com/doc/apis-php/en/apis-php-problems.html" target="_top">Common Problems with MySQL and PHP</a>
        </p></li><li class="listitem"><p>
          <a class="ulink" href="http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes.html" target="_top">Connector/ODBC Notes and Tips</a>
        </p></li><li class="listitem"><p>
          <a class="ulink" href="http://dev.mysql.com/doc/connector-net/en/connector-net-programming.html" target="_top">Connector/Net Programming</a>
        </p></li><li class="listitem"><p>
          <a class="ulink" href="http://dev.mysql.com/doc/connector-j/8.0/en/" target="_top">MySQL Connector/J 8.0 Developer Guide</a>
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-replication"></a>A.13 MySQL 8.0 FAQ: Replication</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139899452303552"></a><p>
      In the following section, we provide answers to questions that are
      most frequently asked about MySQL Replication.
</p>
<div class="qandaset">
<a name="idm139899452301568"></a><dl><dt>A.13.1. <a href="faqs.html#faq-replication-have-connected-slave">
        Must the slave be connected to the master all the time?
      </a></dt><dt>A.13.2. <a href="faqs.html#faq-replication-have-enable-networking">
        Must I enable networking on my master and slave to enable
        replication?
      </a></dt><dt>A.13.3. <a href="faqs.html#faq-replication-how-compare-slave-date">
        How do I know how late a slave is compared to the master? In
        other words, how do I know the date of the last statement
        replicated by the slave?
      </a></dt><dt>A.13.4. <a href="faqs.html#faq-replication-how-block-updates">
        How do I force the master to block updates until the slave
        catches up?
      </a></dt><dt>A.13.5. <a href="faqs.html#faq-replication-how-two-way-problems">
        What issues should I be aware of when setting up two-way
        replication?
      </a></dt><dt>A.13.6. <a href="faqs.html#faq-replication-how-improves-performance">
        How can I use replication to improve performance of my system?
      </a></dt><dt>A.13.7. <a href="faqs.html#faq-replication-how-prepare-for-replication">
        What should I do to prepare client code in my own applications
        to use performance-enhancing replication?
      </a></dt><dt>A.13.8. <a href="faqs.html#faq-replication-how-benefits-me">
        When and how much can MySQL replication improve the performance
        of my system?
      </a></dt><dt>A.13.9. <a href="faqs.html#faq-replication-how-high-availability">
        How can I use replication to provide redundancy or high
        availability?
      </a></dt><dt>A.13.10. <a href="faqs.html#faq-replication-how-know-log-format">
        How do I tell whether a master server is using statement-based
        or row-based binary logging format?
      </a></dt><dt>A.13.11. <a href="faqs.html#faq-replication-how-use-row-based">
        How do I tell a slave to use row-based replication?
      </a></dt><dt>A.13.12. <a href="faqs.html#faq-replication-how-prevent-grant-revoke">
        How do I prevent GRANT and
        REVOKE statements from
        replicating to slave machines?
      </a></dt><dt>A.13.13. <a href="faqs.html#faq-replication-can-mix-os">
        Does replication work on mixed operating systems (for example,
        the master runs on Linux while slaves run on OS X and Windows)?
      </a></dt><dt>A.13.14. <a href="faqs.html#faq-replication-can-mix-arch">
        Does replication work on mixed hardware architectures (for
        example, the master runs on a 64-bit machine while slaves run on
        32-bit machines)?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-replication-have-connected-slave"></a><a name="idm139899452300816"></a><p><b>A.13.1.</b></p></td><td align="left" valign="top"><p>
        Must the slave be connected to the master all the time?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No, it does not. The slave can go down or stay disconnected for
        hours or even days, and then reconnect and catch up on updates.
        For example, you can set up a master/slave relationship over a
        dial-up link where the link is up only sporadically and for
        short periods of time. The implication of this is that, at any
        given time, the slave is not guaranteed to be in synchrony with
        the master unless you take some special measures.
      </p><p>
        To ensure that catchup can occur for a slave that has been
        disconnected, you must not remove binary log files from the
        master that contain information that has not yet been replicated
        to the slaves. Asynchronous replication can work only if the
        slave is able to continue reading the binary log from the point
        where it last read events.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-have-enable-networking"></a><a name="idm139899452297520"></a><p><b>A.13.2.</b></p></td><td align="left" valign="top"><p>
        Must I enable networking on my master and slave to enable
        replication?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes, networking must be enabled on the master and slave. If
        networking is not enabled, the slave cannot connect to the
        master and transfer the binary log. Check that the
        <a class="link" href="server-administration.html#option_mysqld_skip-networking"><code class="option">skip-networking</code></a> option has not
        been enabled in the configuration file for either server.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-compare-slave-date"></a><a name="idm139899452294256"></a><p><b>A.13.3.</b></p></td><td align="left" valign="top"><p>
        How do I know how late a slave is compared to the master? In
        other words, how do I know the date of the last statement
        replicated by the slave?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Check the <code class="literal">Seconds_Behind_Master</code> column in the
        output from <a class="link" href="sql-syntax.html#show-slave-status" title="13.7.6.34 SHOW SLAVE STATUS Syntax"><code class="literal">SHOW SLAVE STATUS</code></a>.
        See <a class="xref" href="replication.html#replication-administration-status" title="17.1.7.1 Checking Replication Status">Section 17.1.7.1, “Checking Replication Status”</a>.
      </p><p>
        When the slave SQL thread executes an event read from the
        master, it modifies its own time to the event timestamp. (This
        is why <a class="link" href="data-types.html#datetime" title="11.3.1 The DATE, DATETIME, and TIMESTAMP Types"><code class="literal">TIMESTAMP</code></a> is well
        replicated.) In the <code class="literal">Time</code> column in the output
        of <a class="link" href="sql-syntax.html#show-processlist" title="13.7.6.29 SHOW PROCESSLIST Syntax"><code class="literal">SHOW PROCESSLIST</code></a>, the number
        of seconds displayed for the slave SQL thread is the number of
        seconds between the timestamp of the last replicated event and
        the real time of the slave machine. You can use this to
        determine the date of the last replicated event. Note that if
        your slave has been disconnected from the master for one hour,
        and then reconnects, you may immediately see large
        <code class="literal">Time</code> values such as 3600 for the slave SQL
        thread in <a class="link" href="sql-syntax.html#show-processlist" title="13.7.6.29 SHOW PROCESSLIST Syntax"><code class="literal">SHOW PROCESSLIST</code></a>. This
        is because the slave is executing statements that are one hour
        old. See <a class="xref" href="replication.html#replication-implementation-details" title="17.2.2 Replication Implementation Details">Section 17.2.2, “Replication Implementation Details”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-block-updates"></a><a name="idm139899452282528"></a><p><b>A.13.4.</b></p></td><td align="left" valign="top"><p>
        How do I force the master to block updates until the slave
        catches up?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Use the following procedure:
</p>
<div class="orderedlist">
<ol class="orderedlist" type="1"><li class="listitem"><p>
            On the master, execute these statements:
          </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>FLUSH TABLES WITH READ LOCK;</code></strong>
mysql&gt; <strong class="userinput"><code>SHOW MASTER STATUS;</code></strong>
</pre><p>
            Record the replication coordinates (the current binary log
            file name and position) from the output of the
            <a class="link" href="sql-syntax.html#show" title="13.7.6 SHOW Syntax"><code class="literal">SHOW</code></a> statement.
          </p></li><li class="listitem"><p>
            On the slave, issue the following statement, where the
            arguments to the
            <a class="link" href="functions.html#function_master-pos-wait"><code class="literal">MASTER_POS_WAIT()</code></a> function
            are the replication coordinate values obtained in the
            previous step:
          </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT MASTER_POS_WAIT('<em class="replaceable"><code>log_name</code></em>', <em class="replaceable"><code>log_pos</code></em>);</code></strong>
</pre><p>
            The <a class="link" href="sql-syntax.html#select" title="13.2.10 SELECT Syntax"><code class="literal">SELECT</code></a> statement blocks
            until the slave reaches the specified log file and position.
            At that point, the slave is in synchrony with the master and
            the statement returns.
          </p></li><li class="listitem"><p>
            On the master, issue the following statement to enable the
            master to begin processing updates again:
          </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>UNLOCK TABLES;</code></strong>
</pre></li></ol>
</div>
</td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-two-way-problems"></a><a name="idm139899452265424"></a><p><b>A.13.5.</b></p></td><td align="left" valign="top"><p>
        What issues should I be aware of when setting up two-way
        replication?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        MySQL replication currently does not support any locking
        protocol between master and slave to guarantee the atomicity of
        a distributed (cross-server) update. In other words, it is
        possible for client A to make an update to co-master 1, and in
        the meantime, before it propagates to co-master 2, client B
        could make an update to co-master 2 that makes the update of
        client A work differently than it did on co-master 1. Thus, when
        the update of client A makes it to co-master 2, it produces
        tables that are different from what you have on co-master 1,
        even after all the updates from co-master 2 have also
        propagated. This means that you should not chain two servers
        together in a two-way replication relationship unless you are
        sure that your updates can safely happen in any order, or unless
        you take care of mis-ordered updates somehow in the client code.
      </p><p>
        You should also realize that two-way replication actually does
        not improve performance very much (if at all) as far as updates
        are concerned. Each server must do the same number of updates,
        just as you would have a single server do. The only difference
        is that there is a little less lock contention because the
        updates originating on another server are serialized in one
        slave thread. Even this benefit might be offset by network
        delays.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-improves-performance"></a><a name="idm139899452261504"></a><p><b>A.13.6.</b></p></td><td align="left" valign="top"><p>
        How can I use replication to improve performance of my system?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Set up one server as the master and direct all writes to it.
        Then configure as many slaves as you have the budget and
        rackspace for, and distribute the reads among the master and the
        slaves. You can also start the slaves with the
        <a class="link" href="innodb-storage-engine.html#option_mysqld_innodb"><code class="option">--skip-innodb</code></a>,
        <a class="link" href="server-administration.html#option_mysqld_low-priority-updates"><code class="option">--low-priority-updates</code></a>, and
        <a class="link" href="server-administration.html#option_mysqld_delay-key-write"><code class="option">--delay-key-write=ALL</code></a> options to
        get speed improvements on the slave end. In this case, the slave
        uses nontransactional <code class="literal">MyISAM</code> tables instead
        of <code class="literal">InnoDB</code> tables to get more speed by
        eliminating transactional overhead.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-prepare-for-replication"></a><a name="idm139899452254672"></a><p><b>A.13.7.</b></p></td><td align="left" valign="top"><p>
        What should I do to prepare client code in my own applications
        to use performance-enhancing replication?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        See the guide to using replication as a scale-out solution,
        <a class="xref" href="replication.html#replication-solutions-scaleout" title="17.3.5 Using Replication for Scale-Out">Section 17.3.5, “Using Replication for Scale-Out”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-benefits-me"></a><a name="idm139899452251888"></a><p><b>A.13.8.</b></p></td><td align="left" valign="top"><p>
        When and how much can MySQL replication improve the performance
        of my system?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        MySQL replication is most beneficial for a system that processes
        frequent reads and infrequent writes. In theory, by using a
        single-master/multiple-slave setup, you can scale the system by
        adding more slaves until you either run out of network
        bandwidth, or your update load grows to the point that the
        master cannot handle it.
      </p><p>
        To determine how many slaves you can use before the added
        benefits begin to level out, and how much you can improve
        performance of your site, you must know your query patterns, and
        determine empirically by benchmarking the relationship between
        the throughput for reads and writes on a typical master and a
        typical slave. The example here shows a rather simplified
        calculation of what you can get with replication for a
        hypothetical system. Let <code class="literal">reads</code> and
        <code class="literal">writes</code> denote the number of reads and writes
        per second, respectively.
      </p><p>
        Let's say that system load consists of 10% writes and 90% reads,
        and we have determined by benchmarking that
        <code class="literal">reads</code> is 1200 - 2 *
        <code class="literal">writes</code>. In other words, the system can do
        1,200 reads per second with no writes, the average write is
        twice as slow as the average read, and the relationship is
        linear. Suppose that the master and each slave have the same
        capacity, and that we have one master and
        <em class="replaceable"><code>N</code></em> slaves. Then we have for each
        server (master or slave):
      </p><p>
        <code class="literal">reads</code> = 1200 - 2 * <code class="literal">writes</code>
      </p><p>
        <code class="literal">reads</code> = 9 * <code class="literal">writes</code> /
        (<em class="replaceable"><code>N</code></em> + 1) (reads are split, but writes
        replicated to all slaves)
      </p><p>
        9 * <code class="literal">writes</code> / (<em class="replaceable"><code>N</code></em> +
        1) + 2 * <code class="literal">writes</code> = 1200
      </p><p>
        <code class="literal">writes</code> = 1200 / (2 +
        9/(<em class="replaceable"><code>N</code></em> + 1))
      </p><p>
        The last equation indicates the maximum number of writes for
        <em class="replaceable"><code>N</code></em> slaves, given a maximum possible
        read rate of 1,200 per second and a ratio of nine reads per
        write.
      </p><p>
        This analysis yields the following conclusions:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            If <em class="replaceable"><code>N</code></em> = 0 (which means we have no
            replication), our system can handle about 1200/11 = 109
            writes per second.
          </p></li><li class="listitem"><p>
            If <em class="replaceable"><code>N</code></em> = 1, we get up to 184 writes
            per second.
          </p></li><li class="listitem"><p>
            If <em class="replaceable"><code>N</code></em> = 8, we get up to 400 writes
            per second.
          </p></li><li class="listitem"><p>
            If <em class="replaceable"><code>N</code></em> = 17, we get up to 480
            writes per second.
          </p></li><li class="listitem"><p>
            Eventually, as <em class="replaceable"><code>N</code></em> approaches
            infinity (and our budget negative infinity), we can get very
            close to 600 writes per second, increasing system throughput
            about 5.5 times. However, with only eight servers, we
            increase it nearly four times.
</p></li></ul>
</div>
<p>
        These computations assume infinite network bandwidth and neglect
        several other factors that could be significant on your system.
        In many cases, you may not be able to perform a computation
        similar to the one just shown that accurately predicts what will
        happen on your system if you add <em class="replaceable"><code>N</code></em>
        replication slaves. However, answering the following questions
        should help you decide whether and by how much replication will
        improve the performance of your system:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            What is the read/write ratio on your system?
          </p></li><li class="listitem"><p>
            How much more write load can one server handle if you reduce
            the reads?
          </p></li><li class="listitem"><p>
            For how many slaves do you have bandwidth available on your
            network?
</p></li></ul>
</div>
</td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-high-availability"></a><a name="idm139899452223648"></a><p><b>A.13.9.</b></p></td><td align="left" valign="top"><p>
        How can I use replication to provide redundancy or high
        availability?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        How you implement redundancy is entirely dependent on your
        application and circumstances. High-availability solutions (with
        automatic failover) require active monitoring and either custom
        scripts or third party tools to provide the failover support
        from the original MySQL server to the slave.
      </p><p>
        To handle the process manually, you should be able to switch
        from a failed master to a pre-configured slave by altering your
        application to talk to the new server or by adjusting the DNS
        for the MySQL server from the failed server to the new server.
      </p><p>
        For more information and some example solutions, see
        <a class="xref" href="replication.html#replication-solutions-switch" title="17.3.8 Switching Masters During Failover">Section 17.3.8, “Switching Masters During Failover”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-know-log-format"></a><a name="idm139899452219520"></a><p><b>A.13.10.</b></p></td><td align="left" valign="top"><p>
        How do I tell whether a master server is using statement-based
        or row-based binary logging format?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Check the value of the
        <a class="link" href="replication.html#sysvar_binlog_format"><code class="literal">binlog_format</code></a> system variable:
      </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW VARIABLES LIKE 'binlog_format';</code></strong>
</pre><p>
        The value shown will be one of <code class="literal">STATEMENT</code>,
        <code class="literal">ROW</code>, or <code class="literal">MIXED</code>. For
        <code class="literal">MIXED</code> mode, statement-based logging is used
        by default but replication switches automatically to row-based
        logging under certain conditions, such as unsafe statements. For
        information about when this may occur, see
        <a class="xref" href="server-administration.html#binary-log-mixed" title="5.4.4.3 Mixed Binary Logging Format">Section 5.4.4.3, “Mixed Binary Logging Format”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-use-row-based"></a><a name="idm139899452210384"></a><p><b>A.13.11.</b></p></td><td align="left" valign="top"><p>
        How do I tell a slave to use row-based replication?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Slaves automatically know which format to use.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-how-prevent-grant-revoke"></a><a name="idm139899452208320"></a><p><b>A.13.12.</b></p></td><td align="left" valign="top"><p>
        How do I prevent <a class="link" href="sql-syntax.html#grant" title="13.7.1.6 GRANT Syntax"><code class="literal">GRANT</code></a> and
        <a class="link" href="sql-syntax.html#revoke" title="13.7.1.8 REVOKE Syntax"><code class="literal">REVOKE</code></a> statements from
        replicating to slave machines?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Start the server with the
        <a class="link" href="replication.html#option_mysqld_replicate-wild-ignore-table"><code class="option">--replicate-wild-ignore-table=mysql.%</code></a>
        option to ignore replication for tables in the
        <code class="literal">mysql</code> database.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-can-mix-os"></a><a name="idm139899452201984"></a><p><b>A.13.13.</b></p></td><td align="left" valign="top"><p>
        Does replication work on mixed operating systems (for example,
        the master runs on Linux while slaves run on OS X and Windows)?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-replication-can-mix-arch"></a><a name="idm139899452199936"></a><p><b>A.13.14.</b></p></td><td align="left" valign="top"><p>
        Does replication work on mixed hardware architectures (for
        example, the master runs on a 64-bit machine while slaves run on
        32-bit machines)?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-thread-pool"></a>A.14 MySQL 8.0 FAQ: MySQL Enterprise Thread Pool</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899452197072"></a><dl><dt>A.14.1. <a href="faqs.html#faq-thread-pool-what-solve">
        What is the Thread Pool and what problem does it solve?
      </a></dt><dt>A.14.2. <a href="faqs.html#faq-thread-pool-how-manage-operation">
        How does the Thread Pool limit and manage concurrent sessions
        and transactions for optimal performance and throughput?
      </a></dt><dt>A.14.3. <a href="faqs.html#faq-thread-pool-how-diff-connector-pool">
        How is the Thread Pool different from the client side Connection
        Pool?
      </a></dt><dt>A.14.4. <a href="faqs.html#faq-thread-pool-when-use">
        When should I use the Thread Pool?
      </a></dt><dt>A.14.5. <a href="faqs.html#faq-thread-pool-what-config-recommend">
        Are there recommended Thread Pool configurations?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-thread-pool-what-solve"></a><a name="idm139899452196304"></a><p><b>A.14.1.</b></p></td><td align="left" valign="top"><p>
        What is the Thread Pool and what problem does it solve?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The MySQL Thread Pool is a MySQL server plugin that extends the
        default connection-handling capabilities of the MySQL server to
        limit the number of concurrently executing statements/queries
        and transactions to ensure that each has sufficient CPU and
        memory resources to fulfill its task. Commercial distributions
        of MySQL 8.0 include the Thread Pool plugin.
      </p><p>
        The default thread-handling model in MySQL Server executes
        statements using one thread per client connection. As more
        clients connect to the server and execute statements, overall
        performance degrades. The Thread Pool plugin provides an
        alternative thread-handling model designed to reduce overhead
        and improve performance. The Thread Pool plugin increases server
        performance by efficiently managing statement execution threads
        for large numbers of client connections, especially on modern
        multi-CPU/Core systems.
      </p><p>
        For more information, see <a class="xref" href="server-administration.html#thread-pool" title="5.6.3 MySQL Enterprise Thread Pool">Section 5.6.3, “MySQL Enterprise Thread Pool”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-thread-pool-how-manage-operation"></a><a name="idm139899452191872"></a><p><b>A.14.2.</b></p></td><td align="left" valign="top"><p>
        How does the Thread Pool limit and manage concurrent sessions
        and transactions for optimal performance and throughput?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The Thread Pool uses a <span class="quote">“<span class="quote">divide and conquer</span>”</span>
        approach to limiting and balancing concurrency. Unlike the
        default connection handling of the MySQL Server, the Thread Pool
        separates connections and threads, so there is no fixed
        relationship between connections and the threads that execute
        statements received from those connections. The Thread Pool then
        manages client connections within configurable thread groups,
        where they are prioritized and queued based on the nature of the
        work they were submitted to accomplish.
      </p><p>
        For more information, see
        <a class="xref" href="server-administration.html#thread-pool-operation" title="5.6.3.3 Thread Pool Operation">Section 5.6.3.3, “Thread Pool Operation”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-thread-pool-how-diff-connector-pool"></a><a name="idm139899452187712"></a><p><b>A.14.3.</b></p></td><td align="left" valign="top"><p>
        How is the Thread Pool different from the client side Connection
        Pool?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The MySQL Connection Pool operates on the client side to ensure
        that a MySQL client does not constantly connect to and
        disconnect from the MySQL server. It is designed to cache idle
        connections in the MySQL client for use by other users as they
        are needed. This minimizes the overhead and expense of
        establishing and tearing down connections as queries are
        submitted to the MySQL server. The MySQL Connection Pool has no
        visibility as to the query handling capabilities or load of the
        back-end MySQL server. By contrast, the Thread Pool operates on
        the MySQL server side and is designed to manage the execution of
        inbound concurrent connections and queries as they are received
        from the client connections accessing the back-end MySQL
        database. Because of the separation of duties, the MySQL
        Connection Pool and Thread Pool are orthogonal and can be used
        independent of each other.
      </p><p>
        MySQL Connection Pooling via the MySQL Connectors is covered in
        <a class="xref" href="connectors-apis.html" title="Chapter 27 Connectors and APIs">Chapter 27, <i>Connectors and APIs</i></a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-thread-pool-when-use"></a><a name="idm139899452183552"></a><p><b>A.14.4.</b></p></td><td align="left" valign="top"><p>
        When should I use the Thread Pool?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        There are a few rules of thumb to consider for optimal Thread
        Pool use cases:
      </p><p>
        The MySQL <a class="link" href="server-administration.html#statvar_Threads_running"><code class="literal">Threads_running</code></a>
        variable keeps track of the number of concurrent statements
        currently executing in the MySQL Server. If this variable
        consistently exceeds a region where the server won't operate
        optimally (usually going beyond 40 for InnoDB workloads), the
        Thread Pool will be beneficial, especially in extreme parallel
        overload situations.
      </p><p>
        If you are using the
        <a class="link" href="innodb-storage-engine.html#sysvar_innodb_thread_concurrency"><code class="literal">innodb_thread_concurrency</code></a> to
        limit the number of concurrently executing statements, you will
        find the Thread Pool solves the same problem, only better, by
        assigning connections to thread groups, then queuing executions
        based on transactional content, user defined designations, and
        so forth.
      </p><p>
        Lastly, if your workload comprises mainly short queries, the
        Thread Pool will be beneficial.
      </p><p>
        To learn more, see <a class="xref" href="server-administration.html#thread-pool-tuning" title="5.6.3.4 Thread Pool Tuning">Section 5.6.3.4, “Thread Pool Tuning”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-thread-pool-what-config-recommend"></a><a name="idm139899452175872"></a><p><b>A.14.5.</b></p></td><td align="left" valign="top"><p>
        Are there recommended Thread Pool configurations?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The Thread Pool has a number of user case driven configuration
        parameters that affect its performance. To learn about these and
        tips on tuning, see <a class="xref" href="server-administration.html#thread-pool-tuning" title="5.6.3.4 Thread Pool Tuning">Section 5.6.3.4, “Thread Pool Tuning”</a>.
</p></td></tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-innodb-change-buffer"></a>A.15 MySQL 8.0 FAQ: InnoDB Change Buffer</h2>

</div>

</div>

</div>
<div class="qandaset">
<a name="idm139899452172304"></a><dl><dt>A.15.1. <a href="faqs.html#faq-innodb-change-buffer-operations">
        What types of operations modify secondary indexes and result in
        change buffering?
      </a></dt><dt>A.15.2. <a href="faqs.html#faq-innodb-change-buffer-benefits">
        What is the benefit of the InnoDB change
        buffer?
      </a></dt><dt>A.15.3. <a href="faqs.html#faq-innodb-change-buffer-index-types">
        Does the change buffer support other types of indexes?
      </a></dt><dt>A.15.4. <a href="faqs.html#faq-innodb-change-buffer-space-max-size">
        How much space does InnoDB use for the change
        buffer?
      </a></dt><dt>A.15.5. <a href="faqs.html#faq-innodb-change-buffer-current-size">
        How do I determine the current size of the change buffer?
      </a></dt><dt>A.15.6. <a href="faqs.html#faq-innodb-change-buffer-merging">
        When does change buffer merging occur?
      </a></dt><dt>A.15.7. <a href="faqs.html#faq-innodb-change-buffer-flush-time">
        When is the change buffer flushed?
      </a></dt><dt>A.15.8. <a href="faqs.html#faq-innodb-change-buffer-when-to-enable">
        When should the change buffer be used?
      </a></dt><dt>A.15.9. <a href="faqs.html#faq-innodb-change-buffer-when-to-disable">
        When should the change buffer not be used?
      </a></dt><dt>A.15.10. <a href="faqs.html#faq-innodb-change-buffer-info">
        Where can I find additional information about the change buffer?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-operations"></a><a name="idm139899452171552"></a><p><b>A.15.1.</b></p></td><td align="left" valign="top"><p>
        What types of operations modify secondary indexes and result in
        change buffering?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, and
        <code class="literal">DELETE</code> operations can modify secondary
        indexes. If an affected index page is not in the buffer pool,
        the changes can be buffered in the change buffer.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-benefits"></a><a name="idm139899452167296"></a><p><b>A.15.2.</b></p></td><td align="left" valign="top"><p>
        What is the benefit of the <code class="literal">InnoDB</code> change
        buffer?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Buffering secondary index changes when secondary index pages are
        not in the buffer pool avoids expensive random access I/O
        operations that would be required to immediately read in
        affected index pages from disk. Buffered changes can be applied
        later, in batches, as pages are read into the buffer pool by
        other read operations.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-index-types"></a><a name="idm139899452164192"></a><p><b>A.15.3.</b></p></td><td align="left" valign="top"><p>
        Does the change buffer support other types of indexes?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No. The change buffer only supports secondary indexes. Clustered
        indexes, full-text indexes, and spatial indexes are not
        supported. Full-text indexes have their own caching mechanism.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-space-max-size"></a><a name="idm139899452162032"></a><p><b>A.15.4.</b></p></td><td align="left" valign="top"><p>
        How much space does <code class="literal">InnoDB</code> use for the change
        buffer?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Prior to the introduction of the
        <a class="link" href="innodb-storage-engine.html#sysvar_innodb_change_buffer_max_size"><code class="literal">innodb_change_buffer_max_size</code></a>
        configuration option in MySQL 5.6, the maximum size of the
        on-disk change buffer in the system tablespace was 1/3 of the
        <code class="literal">InnoDB</code> buffer pool size.
      </p><p>
        In MySQL 5.6 and later, the
        <a class="link" href="innodb-storage-engine.html#sysvar_innodb_change_buffer_max_size"><code class="literal">innodb_change_buffer_max_size</code></a>
        configuration option defines the maximum size of the change
        buffer as a percentage of the total buffer pool size. By
        default,
        <a class="link" href="innodb-storage-engine.html#sysvar_innodb_change_buffer_max_size"><code class="literal">innodb_change_buffer_max_size</code></a>
        is set to 25. The maximum setting is 50.
      </p><p>
        <code class="literal">InnoDB</code> does not buffer an operation if it
        would cause the on-disk change buffer to exceed the defined
        limit.
      </p><p>
        Change buffer pages are not required to persist in the buffer
        pool and may be evicted by LRU operations.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-current-size"></a><a name="idm139899452152272"></a><p><b>A.15.5.</b></p></td><td align="left" valign="top"><p>
        How do I determine the current size of the change buffer?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The current size of the change buffer is reported by
        <code class="literal">SHOW ENGINE INNODB STATUS \G</code>, under the
        <code class="literal">INSERT BUFFER AND ADAPTIVE HASH INDEX</code>
        heading. For example:
      </p><pre data-lang="none" class="programlisting">-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
</pre><p>
        Relevant data points include:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <code class="literal">size</code>: The number of pages used within the
            change buffer. Change buffer size is equal to <code class="literal">seg
            size - (1 + free list len)</code>. The <code class="literal">1
            +</code> value represents the change buffer header page.
          </p></li><li class="listitem"><p>
            <code class="literal">seg size</code>: The size of the change buffer,
            in pages.
</p></li></ul>
</div>
<p>
        For information about monitoring change buffer status, see
        <a class="xref" href="innodb-storage-engine.html#innodb-insert-buffering" title="15.4.2 Change Buffer">Section 15.4.2, “Change Buffer”</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-merging"></a><a name="idm139899452140976"></a><p><b>A.15.6.</b></p></td><td align="left" valign="top"><p>
        When does change buffer merging occur?
</p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top">
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            When a page is read into the buffer pool, buffered changes
            are merged upon completion of the read, before the page is
            made available.
          </p></li><li class="listitem"><p>
            Change buffer merging is performed as a background task. The
            <a class="link" href="innodb-storage-engine.html#sysvar_innodb_io_capacity"><code class="literal">innodb_io_capacity</code></a>
            parameter sets an upper limit on the I/O activity performed
            by <code class="literal">InnoDB</code> background tasks such as
            merging data from the change buffer.
          </p></li><li class="listitem"><p>
            A change buffer merge is performed during crash recovery.
            Changes are applied from the change buffer (in the system
            tablespace) to leaf pages of secondary indexes as index
            pages are read into the buffer pool.
          </p></li><li class="listitem"><p>
            The change buffer is fully durable and will survive a system
            crash. Upon restart, change buffer merge operations resume
            as part of normal operations.
          </p></li><li class="listitem"><p>
            A full merge of the change buffer can be forced as part of a
            slow server shutdown using
            <a class="link" href="innodb-storage-engine.html#sysvar_innodb_fast_shutdown"><code class="literal">--innodb-fast-shutdown=0</code></a>.
</p></li></ul>
</div>
</td></tr><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-flush-time"></a><a name="idm139899452130880"></a><p><b>A.15.7.</b></p></td><td align="left" valign="top"><p>
        When is the change buffer flushed?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Updated pages are flushed by the same flushing mechanism that
        flushes the other pages that occupy the buffer pool.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-when-to-enable"></a><a name="idm139899452128800"></a><p><b>A.15.8.</b></p></td><td align="left" valign="top"><p>
        When should the change buffer be used?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The change buffer is a feature designed to reduce random I/O to
        secondary indexes as indexes grow larger and no longer fit in
        the <code class="literal">InnoDB</code> buffer pool. Generally, the change
        buffer should be used when the entire data set does not fit into
        the buffer pool, when there is substantial DML activity that
        modifies secondary index pages, or when there are lots of
        secondary indexes that are regularly changed by DML activity.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-when-to-disable"></a><a name="idm139899452125568"></a><p><b>A.15.9.</b></p></td><td align="left" valign="top"><p>
        When should the change buffer not be used?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        You might consider disabling the change buffer if the entire
        data set fits within the <code class="literal">InnoDB</code> buffer pool,
        if you have relatively few secondary indexes, or if you are
        using solid-state storage, where random reads are about as fast
        as sequential reads. Before making configuration changes, it is
        recommended that you run tests using a representative workload
        to determine if disabling the change buffer provides any
        benefit.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-innodb-change-buffer-info"></a><a name="idm139899452122448"></a><p><b>A.15.10.</b></p></td><td align="left" valign="top"><p>
        Where can I find additional information about the change buffer?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        The following sections and blog post provide additional
        information about the <code class="literal">InnoDB</code> change buffer:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            <a class="xref" href="innodb-storage-engine.html#innodb-insert-buffering" title="15.4.2 Change Buffer">Section 15.4.2, “Change Buffer”</a>
          </p></li><li class="listitem"><p>
            <a class="xref" href="innodb-storage-engine.html#innodb-performance-change_buffering" title="15.6.4 Configuring InnoDB Change Buffering">Section 15.6.4, “Configuring InnoDB Change Buffering”</a>
          </p></li><li class="listitem"><p>
            <a class="ulink" href="https://blogs.oracle.com/mysqlinnodb/entry/mysql_5_5_innodb_change" target="_top">MySQL
            5.5: InnoDB Change Buffering</a>
</p></li></ul>
</div>
</td></tr></tbody></table>
</div>

</div>

<div class="section">

<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-tablespace-encryption"></a>A.16 MySQL 8.0 FAQ: InnoDB Tablespace Encryption</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139899452114112"></a>
<div class="qandaset">
<a name="idm139899452112656"></a><dl><dt>A.16.1. <a href="faqs.html#faq-tablespace-encryption-access">
        Is data decrypted for users who are authorized to see it?
      </a></dt><dt>A.16.2. <a href="faqs.html#faq-tablespace-encryption-overhead">
        What is the overhead associated with InnoDB
        tablespace encryption?
      </a></dt><dt>A.16.3. <a href="faqs.html#faq-tablespace-encryption-algorithm">
        What are the encryption algorithms used with
        InnoDB tablespace encryption?
      </a></dt><dt>A.16.4. <a href="faqs.html#faq-tablespace-encryption-other-algorithms">
        Is it possible to use 3rd party encryption algorithms in place
        of the one provided by the InnoDB tablespace
        encryption feature?
      </a></dt><dt>A.16.5. <a href="faqs.html#faq-tablespace-encryption-indexed-columns">
        Can indexed columns be encrypted?
      </a></dt><dt>A.16.6. <a href="faqs.html#faq-tablespace-encryption-data-types">
        What data types and data lengths does InnoDB
        tablespace encryption support?
      </a></dt><dt>A.16.7. <a href="faqs.html#faq-tablespace-encryption-network">
        Does data remain encrypted on the network?
      </a></dt><dt>A.16.8. <a href="faqs.html#faq-tablespace-encryption-database-memory">
        Does database memory contain clear-text or encrypted data?
      </a></dt><dt>A.16.9. <a href="faqs.html#faq-tablespace-encryption-data-to-encrypt">
        How do I know which data to encrypt?
      </a></dt><dt>A.16.10. <a href="faqs.html#faq-tablespace-encryption-mysql-encryption">
        How is InnoDB tablespace encryption different
        from encryption functions MySQL already provides?
      </a></dt><dt>A.16.11. <a href="faqs.html#faq-tablespace-encryption-transportable-tablespaces">
        Does the transportable tablespaces feature work with
        InnoDB tablespace encryption?
      </a></dt><dt>A.16.12. <a href="faqs.html#faq-tablespace-encryption-compression">
        Does compression work with InnoDB tablespace
        encryption?
      </a></dt><dt>A.16.13. <a href="faqs.html#faq-tablespace-encryption-mysqldump">
        Can I use mysqlpump or
        mysqldump with encrypted tables?
      </a></dt><dt>A.16.14. <a href="faqs.html#faq-tablespace-encryption-key-rotation">
        How do I change (rotate, re-key) the master encryption key?
      </a></dt><dt>A.16.15. <a href="faqs.html#faq-tablespace-encryption-data-migration">
        How do I migrate data from a clear-text
        InnoDB tablespace to an encrypted
        InnoDB tablespace?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-access"></a><a name="idm139899452111904"></a><p><b>A.16.1.</b></p></td><td align="left" valign="top"><p>
        Is data decrypted for users who are authorized to see it?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. <code class="literal">InnoDB</code> tablespace encryption is designed
        to provide customers with the ability to transparently apply
        encryption within the database without impacting existing
        applications. Returning data in encrypted format would break
        most existing applications. <code class="literal">InnoDB</code> tablespace
        encryption provides the benefit of encryption without the
        overhead associated with traditional database encryption
        solutions, which would typically require expensive and
        substantial changes to applications, database triggers, and
        views.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-overhead"></a><a name="idm139899452107952"></a><p><b>A.16.2.</b></p></td><td align="left" valign="top"><p>
        What is the overhead associated with <code class="literal">InnoDB</code>
        tablespace encryption?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        There is no additional storage overhead. According to internal
        benchmarks, performance overhead amounts to a single digit
        percentage difference.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-algorithm"></a><a name="idm139899452105104"></a><p><b>A.16.3.</b></p></td><td align="left" valign="top"><p>
        What are the encryption algorithms used with
        <code class="literal">InnoDB</code> tablespace encryption?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        <code class="literal">InnoDB</code> tablespace encryption supports the
        Advanced Encryption Standard (AES256) block-based encryption
        algorithm. It uses Electronic Codebook (ECB) block encryption
        mode for tablespace key encryption and Cipher Block Chaining
        (CBC) block encryption mode for data encryption.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-other-algorithms"></a><a name="idm139899452101376"></a><p><b>A.16.4.</b></p></td><td align="left" valign="top"><p>
        Is it possible to use 3rd party encryption algorithms in place
        of the one provided by the <code class="literal">InnoDB</code> tablespace
        encryption feature?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        No, it is not possible to use other encryption algorithms. The
        provided encryption algorithm is broadly accepted.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-indexed-columns"></a><a name="idm139899452098384"></a><p><b>A.16.5.</b></p></td><td align="left" valign="top"><p>
        Can indexed columns be encrypted?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        <code class="literal">InnoDB</code> tablespace encryption supports all
        indexes transparently.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-data-types"></a><a name="idm139899452095696"></a><p><b>A.16.6.</b></p></td><td align="left" valign="top"><p>
        What data types and data lengths does <code class="literal">InnoDB</code>
        tablespace encryption support?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        <code class="literal">InnoDB</code> tablespace encryption supports all
        supported data types. There is no data length limitation.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-network"></a><a name="idm139899452092208"></a><p><b>A.16.7.</b></p></td><td align="left" valign="top"><p>
        Does data remain encrypted on the network?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Data encrypted by the <code class="literal">InnoDB</code> tablespace
        encryption feature is decrypted when it is read from the
        tablespace file. Thus, if the data is on the network, it is in
        clear-text form. However, data on the network can be encrypted
        using MySQL network encryption, which encrypts data traveling to
        and from a database using SSL/TLS.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-database-memory"></a><a name="idm139899452089136"></a><p><b>A.16.8.</b></p></td><td align="left" valign="top"><p>
        Does database memory contain clear-text or encrypted data?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        With <code class="literal">InnoDB</code> tablespace encryption, in-memory
        data is decrypted, which provides 100% transparency.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-data-to-encrypt"></a><a name="idm139899452086368"></a><p><b>A.16.9.</b></p></td><td align="left" valign="top"><p>
        How do I know which data to encrypt?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Compliance with the PCI-DSS standard requires that credit card
        numbers (Primary Account Number, or 'PAN') be stored in
        encrypted form. Breach Notification Laws (for example, CA SB
        1386, CA AB 1950, and similar laws in 43+ more US states)
        require encryption of first name, last name, driver license
        number, and other PII data. In early 2008, CA AB 1298 added
        medical and health insurance information to PII data.
        Additionally, industry specific privacy and security standards
        may require encryption of certain assets. For example, assets
        such as pharmaceutical research results, oil field exploration
        results, financial contracts, or personal data of law
        enforcement informants may require encryption. In the health
        care industry, the privacy of patient data, health records and
        X-ray images is of the highest importance.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-mysql-encryption"></a><a name="idm139899452083392"></a><p><b>A.16.10.</b></p></td><td align="left" valign="top"><p>
        How is <code class="literal">InnoDB</code> tablespace encryption different
        from encryption functions MySQL already provides?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        There are symmetric and asymmetric encryption APIs in MySQL that
        can be used to manually encrypt data within the database.
        However, the application must manage encryption keys and perform
        required encryption and decryption operations by calling API
        functions. <code class="literal">InnoDB</code> tablespace encryption
        requires no application changes, is transparent to end users,
        and provides automated, built-in key management.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-transportable-tablespaces"></a><a name="idm139899452079488"></a><p><b>A.16.11.</b></p></td><td align="left" valign="top"><p>
        Does the transportable tablespaces feature work with
        <code class="literal">InnoDB</code> tablespace encryption?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. For more information, see
        <a class="xref" href="innodb-storage-engine.html#innodb-tablespace-encryption-exporting" title="Exporting Encrypted Tables">Exporting Encrypted Tables</a>.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-compression"></a><a name="idm139899452075872"></a><p><b>A.16.12.</b></p></td><td align="left" valign="top"><p>
        Does compression work with <code class="literal">InnoDB</code> tablespace
        encryption?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Customers using <code class="literal">InnoDB</code> tablespace encryption
        receive the full benefit of compression because compression is
        applied before data blocks are encrypted.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-mysqldump"></a><a name="idm139899452072336"></a><p><b>A.16.13.</b></p></td><td align="left" valign="top"><p>
        Can I use <a class="link" href="programs.html#mysqlpump" title="4.5.6 mysqlpump — A Database Backup Program"><span class="command"><strong>mysqlpump</strong></span></a> or
        <code class="literal">mysqldump</code> with encrypted tables?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Yes. Because these utilities create logical backups, the data
        dumped from encrypted tables is not encrypted.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-key-rotation"></a><a name="idm139899452068336"></a><p><b>A.16.14.</b></p></td><td align="left" valign="top"><p>
        How do I change (rotate, re-key) the master encryption key?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        <code class="literal">InnoDB</code> tablespace encryption uses a two tier
        key mechanism. When tablespace encryption is used, individual
        tablespace keys are stored in the header of the underlying
        tablespace data file. Tablespace keys are encrypted using the
        master encryption key. The master encryption key is generated
        when tablespace encryption is enabled, and is stored outside the
        database. The master encryption key is rotated using the
        <a class="link" href="sql-syntax.html#alter-instance" title="13.1.5 ALTER INSTANCE Syntax"><code class="literal">ALTER INSTANCE
        ROTATE INNODB MASTER KEY</code></a> statement, which generates a
        new master encryption key, stores the key, and rotates the key
        into use.
      </p></td></tr><tr class="question"><td align="left" valign="top"><a name="faq-tablespace-encryption-data-migration"></a><a name="idm139899452063776"></a><p><b>A.16.15.</b></p></td><td align="left" valign="top"><p>
        How do I migrate data from a clear-text
        <code class="literal">InnoDB</code> tablespace to an encrypted
        <code class="literal">InnoDB</code> tablespace?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        Transferring data from one tablespace to another is not
        required. To encrypt data in an <code class="literal">InnoDB</code>
        tablespace, run
        <a class="link" href="sql-syntax.html#alter-table" title="13.1.8 ALTER TABLE Syntax"><code class="literal">ALTER TABLE
        <em class="replaceable"><code>tbl_name</code></em> ENCRYPTION='Y'</code></a>.
        <code class="literal">InnoDB</code> tablespace encryption is only
        supported with
        <a class="link" href="glossary.html#glos_file_per_table" title="file-per-table">file-per-table</a>
        tablespaces.
</p></td></tr></tbody></table>
</div>

</div>

<div class="section">

<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="faqs-virtualization"></a>A.17 MySQL 8.0 FAQ: Virtualization Support</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139899452055472"></a>
<div class="qandaset">
<a name="idm139899452054016"></a><dl><dt>A.17.1. <a href="faqs.html#faq-virtualization-support">
        Is MySQL supported on virtualized environments such as Oracle
        VM, VMWare, Docker, Microsoft Hyper-V, or others?
      </a></dt></dl><table border="0" style="width: 100%;"><colgroup><col align="left" width="1%"><col></colgroup><tbody><tr class="question"><td align="left" valign="top"><a name="faq-virtualization-support"></a><a name="idm139899452053264"></a><p><b>A.17.1.</b></p></td><td align="left" valign="top"><p>
        Is MySQL supported on virtualized environments such as Oracle
        VM, VMWare, Docker, Microsoft Hyper-V, or others?
      </p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>
        MySQL is supported on virtualized environments, but is certified
        only for
        <a class="ulink" href="http://www.oracle.com/technetwork/server-storage/vm/index.html" target="_top">Oracle
        VM</a>. Contact Oracle Support for more information.
      </p><p>
        Be aware of potential problems when using virtualization
        software. The usual ones are related to performance, performance
        degradations, slowness, or unpredictability of disk, I/O,
        network, and memory.
</p></td></tr></tbody></table>
</div>

</div>

</div>
<div class="copyright-footer">

</div>
<div class="navfooter">
<hr>
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="workbench.html">Prev</a></td>
<td width="20%" align="center"><a accesskey="u" href="">Up</a></td>
<td width="40%" align="right"> <a accesskey="n" href="error-handling.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Chapter 30 MySQL Workbench</td>
<td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
<td width="40%" align="right" valign="top">Appendix B Errors, Error Codes, and Common Problems</td>
</tr>
</table>
</div>
</body>
</html>
